VLookup?
 

Hi,

In Column A I have a list of random numbers, in Column B I have the words
1st, 2nd 3rd right upto 90th. In column C I have the following formula which
substracts an amount from column A depending on whats in column B.

=IF(B1="7th",602-A1,IF(B1="6th",518-A1,IF(B1="5th",434-A1,IF(B1="4th",350-A1,IF(B1="3rd",266-A1,IF(B1="2nd",182-A1,IF(B1="1st",98-A1,"NA")))))))

I need to go upto 90th instance, any suggestions?

VLookup?
 

Create a list in columns E & F like this
1st 98
2nd 182
3rd 266
etc. all the way up to 90th

Your formula in column C can then be
=VLOOKUP(B1,E1:F90,2,False)-A1

"Ward" wrote:

Hi,

In Column A I have a list of random numbers, in Column B I have the words
1st, 2nd 3rd right upto 90th. In column C I have the following formula which
substracts an amount from column A depending on whats in column B.

=IF(B1="7th",602-A1,IF(B1="6th",518-A1,IF(B1="5th",434-A1,IF(B1="4th",350-A1,IF(B1="3rd",266-A1,IF(B1="2nd",182-A1,IF(B1="1st",98-A1,"NA")))))))

I need to go upto 90th instance, any suggestions?

VLookup?
 

Ward wrote:
Hi,

In Column A I have a list of random numbers, in Column B I have the words
1st, 2nd 3rd right upto 90th. In column C I have the following formula which
substracts an amount from column A depending on whats in column B.

=IF(B1="7th",602-A1,IF(B1="6th",518-A1,IF(B1="5th",434-A1,IF(B1="4th",350-A1,IF(B1="3rd",266-A1,IF(B1="2nd",182-A1,IF(B1="1st",98-A1,"NA")))))))

I need to go upto 90th instance, any suggestions?


Actually, your formula subtracts the value in A from the amount. Assuming that
is what you want:

=LEFT(B1,LEN(B1)-2)*84+14-A1