Excel code 1

[bilbonvidia]

If in A1 there is a code say 1234M can I get another cell to perform a calculation on the number part of the code? And how could I make it so if there are two or more possible calculations that can be performed depending on the Letter, the correct one is performed. Thanks!

 

[AnotherHiggins]

However you proceed, you'll be using logic. So you (and we, if we are to help you) need to know what logic we can use to split the data in the cell.

What are all of the possible formats of the code?
- Is there always only one letter at the end of the code?
- Will the letter ever be elsewhere in the code?

As a starting point, consider something like this:

=if(right(A1, 1) = "M" , value(left(A1, len(A1) - 1)) + 1, value(left(A1, len(A1) - 1)) -1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bilbonvidia]

Thanks for the quick reply. There will always only be one letter and it will always be in the same place but there could be 3 or 4 numbers.

 

[yogia]

Hi bilbonvidia:

Please post some sample data along with your expected result(s) and, as John has already stated, the logic asociated with your result(s) ... so we can see how your data is laid out and what you are trying to accomplish.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

[tab]"there could be 3 or 4 numbers"

FYI: The formula I posted will take however many numbers there are. It looks at everything but the last character.

As Yogia said, we'll need more details for a more thorough answer.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bilbonvidia]

This formula is producing results I expected thank you. Here is the formula I have at the moment:

=IF(F8="sb",B10,IF(F8="nt",0,IF(F8="HR",B10,IF(B10>((LEFT(F8,LEN(F8)-1)*10)+9)/12,B10-(((LEFT(F8,LEN(F8)-1)*10)+9)/12),0))))

-----

In another cell I have:

=IF(F8="HR",0,IF(F8=408,0,IF(D10<=B4/12,D10*A4,(B4/12)*A4)))

I want to make it so that when IF(RIGHT(F8,1)="m", the value of b4 in the formuala is doubled. b4 * 2. I dont want the actual value of the cell b4 to be doubled.



is this possible?

 

[bilbonvidia]

EDIT

This formula is producing results I expected thank you. Here is the formula I have at the moment:

=IF(F8="sb",B10,IF(F8="nt",0,IF(F8="HR",B10,IF(B10>((LEFT(F8,LEN(F8)-1)*10)+9)/12,B10-(((LEFT(F8,LEN(F8)-1)*10)+9)/12),0))))

-----

In another cell I have:

=IF(F8="HR",0,IF(F8=408,0,IF(D10<=B4/12,D10*A4,(B4/12)*A4)))

This is now:

=IF(F8="HR",0,IF(RIGHT(F8,1)="n",0,IF(D10<=B4/12,D10*A4,(B4/12)*A4)))



I still want to make it so that when IF(RIGHT(F8,1)="m", the value of b4 in the formuala is doubled. b4 * 2. I dont want the actual value of the cell b4 to be doubled.



is this possible?

 

[Gavona]

Slight simplification of your first formula by using OR:
=IF(OR(F8="sb",F8="HR"),B10,IF(F8="nt",0,IF(B10>((LEFT(F8,LEN(F8)-1)*10)+9)/12,B10-(((LEFT(F8,LEN(F8)-1)*10)+9)/12),0)))

You could simply add another IF to resolve your second issue (I have also removed some unnecessary brackets). Formula is untested.

=IF(OR(F8="HR",RIGHT(F8,1)="n"),0,IF(RIGHT(F8,1)="m",IF(D10<=B4/6,D10*A4,B4/6*A4),IF(D10<=B4/12,D10*A4,B4/12*A4)))

If you need formulae as complex as this then consider:
1. A helper cell or two containing b4/12 for example
2. Naming the fixed reference cells and then using the names in the formulae e.g. =(if(OR(TaxCode="HR",.......LowBandpm*.....
by no means perfect but this is an example of part of a formula I inherited but added named ranges to (UK National Insurance):
IF((B35)<=EER_Thresh*12,0,(B35)-EER_Thresh*12)*(VLOOKUP(B35,MonthlyPay,3,TRUE))+(MAX(0,(B35)-UEL*12)*UEL_Multiplier)-(IF((B35)<=EER_Thresh*12,0,(Band2_To-Band2_From)*12*NIC_Rebate))
3.Consider a user defined function rather than a formula (that is what I should have done in the example above). For advice on this post on Forum707

Gavin

 

[bilbonvidia]

Thank you. I had been trying to use another IF statement but could not get it to work, missing parenthesis or too many arguments but now it is fine. Like you say its getting a bit complex and helper cells would help to cut down on the amount of code, brackets etc. Also I like the idea of naming cells, it would make it much easier when making changes and knowing what is what.

VBA is on the to learn list, may as well start now. Thanks!

 

[Gavona]

Glad to have been of help. If you do go the VBA route then look at the Select Case as an alernative to multiple IFs..... And use Forum707



Gavin