Forum Discussion
Solved
if formula nested
Hello,
I try to transcribe these conditions with an IF but an error message appears that I don't understand
| PA | PN |
| 0,01<PA<0,99 | =(PA*2)+7 |
| 1<=PA<9,99 | =(PA*2)+9 |
| 10<=PA<19,99 | =(PA*2)+11 |
| 20<=PA<29,99 | =(PA*2)+13 |
| PA>=30 | =(PA+40) |
| PA | PN | |||||||||
| 0,02 |
=si(A2>30;(a2+40);si(a2>=20;a2<=29,99:(a2*2)+13;si(a2>10;a2<=19,99:(a2*2)+11;si(a2>1;a2<=9,99;(a2*2)+9;si(a2>0,01;a2<=0,99;(a2*2)+7)))))))))))
|
|||||||||
| 12,43 | ||||||||||
| 24,56 | ||||||||||
| 36 | ||||||||||
| 41,56 |
|
|
||||||||
Yamina,
two alternatives.
0,02 7,04 7,04 12,43 35,86 35,86 24,56 62,12 62,12 36 76 76 41,56 81,56 81,56 0,01 2 7 1 2 9 10 2 11 20 2 13 30 1 40 =IF(A2>=30,A2+40,A2*2+5+MATCH(A2,$B$10:$B$14,1)*2)
=A2*LOOKUP(A2,$B$10:$B$14,$C$10:$C$14)+LOOKUP(A2,$B$10:$B$14,$D$10:$D$14)
5 Replies
Yamina,
two alternatives.
0,02 7,04 7,04 12,43 35,86 35,86 24,56 62,12 62,12 36 76 76 41,56 81,56 81,56 0,01 2 7 1 2 9 10 2 11 20 2 13 30 1 40 =IF(A2>=30,A2+40,A2*2+5+MATCH(A2,$B$10:$B$14,1)*2)
=A2*LOOKUP(A2,$B$10:$B$14,$C$10:$C$14)+LOOKUP(A2,$B$10:$B$14,$D$10:$D$14)
- Thank you for you reply, but I try the two formulas and no results
PA PN
6 #REF!
Here the formula =A2*RECHERCHEV(A2;$B$10:$B$14;$C$10:$C$14)+RECHERCHEV(A2;$B$10:$B$14;$D$10:$D$14)
12,43
24,56
36
41,56
0,01 2 7
1 2 9
10 2 11
20 2 13
30 1 40- Finally I find my error in the first formula I change MATCH by EQUIV in french and it works.
thank you very much.
Hi Yamina,
Here is the formula you need,
=IF(A2>=30,A2+40,IF(A2>=20,A2*2+13,IF(A2>=10,A2*2+11,IF(A2>=1,A2*2+9,IF(A2<0.01,"Less than 0,01",A2*2+7)))))I hope it will help you,
If you like it , please Mark as Best Response- Hi Jihad,
Thank you for your answer, but when I copy your formula in my sheet I have the same error message.
