calculation of column with if condition

[ganeshm]

hi . . .

i have posted a sample file where the formula is
=IF(A3>=90,(C3*100%),IF(A3>=75,(C3*75%),IF(A3>=60,(C3*50%),IF(A3>=50,(C3*25%),IF(A3<=49,0))))).

Note:
1. when column A calculates column C the above formula throws correct value.

2. when column C is empty, the if condition should automatically take column B for calculation.

How should i specify that?

 

[kchiba]

Try this:

=IF(A3>=90,(C3*100%)+(B3*100%),IF(A3>=75,(C3*75%)+(B3*75%),IF(A3>=60,(C3*50%)+(B3*50%),IF(A3>=50,(C3*25%)+(B3*25%),IF(A3<=49,0)))))

Note: This formula assumes that EITHER C or B will have a value, not both

cheers
kanti

 

[oldchippy]

Hi ganeshm,

Try this
=IF(C3="",IF(A3>=90,(B3*100%),IF(A3>=75,(B3*75%),IF(A3>=60,(B3*50%),IF(A3>=50,(B3*25%),IF(A3<=49,0))))),IF(A3>=90,(C3*100%),IF(A3>=75,(C3*75%),IF(A3>=60,(C3*50%),IF(A3>=50,(C3*25%),IF(A3<=49,0))))))

 

[Faseeh]

Try this as well:

=LOOKUP(A3,{49,50,60,75,90,200},{0,25,50,75,100,200})*IF(ISBLANK(C3),B3,C3)/100