Multiple IF(AND

[MickyMouse123]

Hi,
Am trying to use multiple cells to calculate a value in a cell. See the attachment =IF($C$3=$L$3,L7,L7*95%) this formula works just fine.
But when I put in the second part IF(AND(C3=L4,D3=M4),L7*90%) it gives an error.
Not sure if the IF(AND function is written correctly.

Here is a pseudo code for the same. Let me know if this helps.
Start At the beginning C3, D3, E3, F3, G3 and H3 will be "ALL"
Next Step - IF C3=N4, the value in F7 should be equal to I7 * 95%
Next Step - IF C3=N4 and D3=O4, the value in F7 should be equal to I7 * 90%
Next Step - IF C3=N4, D3=O4 and E3=P4, the value in F7 should be equal to I7 * 85%
Next Step - IF C3=N4, D3=O4, E3=P4 and F3=Q4, the value in F7 should be equal to I7 * 80%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4 and G3=R4, the value in F7 should be equal to I7 * 75%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4,G3=R4 and H3=S4, the value in F7 should be equal to I7 * 70%

Thanks

 

[nemmi69]

The and looks OK, what is the error that you get?

 

[MickyMouse123]

The and looks OK, what is the error that you get?

I am able to write the following formula: =IF($C$3=$N$3,N7,IF(AND(C3=N4,D3=O4),N7*90%,N7*95%))
Am not able to build on this. That is I am not able to create multiple IF(AND statements. I do not know how to do so.
The ones in green below is what I have been able to do in the formula above. The rest in need help with.
Next Step - IF C3=N4, the value in F7 should be equal to I7 * 95%
Next Step - IF C3=N4 and D3=O4, the value in F7 should be equal to I7 * 90%
Next Step - IF C3=N4, D3=O4 and E3=P4, the value in F7 should be equal to I7 * 85%
Next Step - IF C3=N4, D3=O4, E3=P4 and F3=Q4, the value in F7 should be equal to I7 * 80%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4 and G3=R4, the value in F7 should be equal to I7 * 75%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4,G3=R4 and H3=S4, the value in F7 should be equal to I7 * 70%

 

[Sufiyan97]

Try

=IF($C$3=$N$4,I7*0.95,IF(AND($C$3=$N$4,D3=O4),I7*0.9,IF(AND($C$3=N4,D3=O4,E3=P4),I7*0.85,IF(AND($C$3=$N$4,D3=O4,E3=P4,F3=Q4),I7*0.8,IF(AND($C$3=$N$4,D3=O4,E3=P4,F3=Q4,G3=R4),I7*0.75,I7*0.7)))))

 

[MickyMouse123]

Try

=IF($C$3=$N$4,I7*0.95,IF(AND($C$3=$N$4,D3=O4),I7*0.9,IF(AND($C$3=N4,D3=O4,E3=P4),I7*0.85,IF(AND($C$3=$N$4,D3=O4,E3=P4,F3=Q4),I7*0.8,IF(AND($C$3=$N$4,D3=O4,E3=P4,F3=Q4,G3=R4),I7*0.75,I7*0.7)))))

Thank you for the input. There is one detail I did not give in the psudeo code. The first line reads like this.
At the beginning C3, D3, E3, F3, G3 and H3 will be "ALL". If TRUE F7=I7. I have added the condition in bold.
Am not sure where to put this (If TRUE F7=I7), at the beginning or at the end. But, because this condition is not put in there, I think what is happening is F7 is showing a value of 42%.
Also, I think there is one more condition that should be there. That is "If the value in C3 - "ALL", then keep the the value of F7=I7.
Am not sure if I was able to articulate this appropriately so that you can understand it.
Psudeo Code:
Start - At the beginning C3, D3, E3, F3, G3 and H3 will be "ALL". If TRUE F7=I7
Check - If C3="ALL", F7=I7
Next Step - IF C3=N4, the value in F7 should be equal to I7 * 95%
Next Step - IF C3=N4 and D3=O4, the value in F7 should be equal to I7 * 90%
Next Step - IF C3=N4, D3=O4 and E3=P4, the value in F7 should be equal to I7 * 85%
Next Step - IF C3=N4, D3=O4, E3=P4 and F3=Q4, the value in F7 should be equal to I7 * 80%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4 and G3=R4, the value in F7 should be equal to I7 * 75%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4,G3=R4 and H3=S4, the value in F7 should be equal to I7 * 70%

 

[Sufiyan97]

So if any of cells C3, D3, E3, F3, G3 and H3 has a text "ALL" then you want to return the value of I7 right?

 

[jdellasala]

Hi,
Am trying to use multiple cells to calculate a value in a cell. See the attachment =IF($C$3=$L$3,L7,L7*95%) this formula works just fine.
But when I put in the second part IF(AND(C3=L4,D3=M4),L7*90%) it gives an error.
Not sure if the IF(AND function is written correctly.

Here is a pseudo code for the same. Let me know if this helps.
Start At the beginning C3, D3, E3, F3, G3 and H3 will be "ALL"
Next Step - IF C3=N4, the value in F7 should be equal to I7 * 95%
Next Step - IF C3=N4 and D3=O4, the value in F7 should be equal to I7 * 90%
Next Step - IF C3=N4, D3=O4 and E3=P4, the value in F7 should be equal to I7 * 85%
Next Step - IF C3=N4, D3=O4, E3=P4 and F3=Q4, the value in F7 should be equal to I7 * 80%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4 and G3=R4, the value in F7 should be equal to I7 * 75%
Next Step - IF C3=N4, D3=O4, E3=P4, F3=Q4,G3=R4 and H3=S4, the value in F7 should be equal to I7 * 70%

Thanks

Not for nothing, but why is C3 and L3 absolute ($C$3 and $L$3) in the first formula, but naked (C3 and L4?) in the second?
Also, the IF has no "ELSE" which may have been mentioned before. The formula should be

IF( AND(C3=L4, D3=M4), L7*90%, "NOT EQUAL")

or something unless you're looking for it to return FALSE - the Binary value, not the text "FALSE".

 

[MickyMouse123]

So if any of cells C3, D3, E3, F3, G3 and H3 has a text "ALL" then you want to return the value of I7 right?

Nope, all cells (C3 to H3) when they have the value ALL, then return the value in I7.

 

[MickyMouse123]

Not for nothing, but why is C3 and L3 absolute ($C$3 and $L$3) in the first formula, but naked (C3 and L4?) in the second?
Also, the IF has no "ELSE" which may have been mentioned before. The formula should be

IF( AND(C3=L4, D3=M4), L7*90%, "NOT EQUAL")

or something unless you're looking for it to return FALSE - the Binary value, not the text "FALSE".

Not for nothing, but why is C3 and L3 absolute ($C$3 and $L$3) in the first formula, but naked (C3 and L4?) in the second? - That's a typo. It should be $C$3 and $L$3
Or something unless you're looking for it to return FALSE - the Binary value, not the text "FALSE". - Am not looking for it to return "FALSE".
IF( AND(C3=L4, D3=M4), L7*90%, "NOT EQUAL") - Should this be at the very beginning of the formula you had provided earlier?

 

[Sufiyan97]

Nope, all cells (C3 to H3) when they have the value ALL, then return the value in I7.

Still not getting
Can you manually type some sample data and expected results and post here via xl2bb?