multiple formula for one cell

[[email protected]]

Hi All
Is it possible to use 3 'if,then' formulas to calculate one number.
Example
A1 = percent; can be anything between 1 and 100
B1 = load; can be 1 or 0
C1= location; can be 1 or 0
D1= level; can be 1 or 0

I am trying to calcute E1 (index) based on multiplying A1 times the relevant 'multiplier' detemined from the if,then of cells B1 through D1.

The formula is based on what's in cells A1, B1, C1, and D1--
If B1 = 1 then the multiplier is 0.5; if A1 = 0 then the multiplier is 1
If C1 = 1 then the multiplier is 1.2; if B1 =0 then the multiplier is 1
If D1 = 1 then the multipler is 2; if D1 =0 then the multipler is 1
So... what I want is a formula that multiples A1 times (either 0.5 or 1) times (either 1.2 or 1) times (either 2 or 1) to give me an index in E1

For example
If A1 = 60% and B1 =1 and C1=1 and D1=0
then E1 (the index) should be 60% x 0.5 x 1.2 x 1 = 0.36.

Hope this makes sense and thanks for any help.

[ConneXionLost]

Try this:

Please Login or Register  to view this content.

[TMS]

I don't follow your conditions. I thought A1 was a percentage yet you say if it is 0 then the multiplier is 1.

Could be just =A1 * IF(B1=1,0.5,1) * IF(C1=1,1.2,1) * IF(D1=1,2,1)


Regards, TMS

[[email protected]]

thanks, but not quite right, index values doubled.

[[email protected]]

sorry for the mix up . but your formula works- fantastic! Thanks for your help and quick reply.

[TMS]

Both formulae are essentially the same though CXL has used a neat Boolean shortcut.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Thanks for the rep.