SOLVED

if formula nested

Copper Contributor

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  

 

       

 

     
5 Replies

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

best response confirmed by Yamina Azri (Copper Contributor)
Solution

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)

 

 

 

Hi Jihad,
Thank you for your answer, but when I copy your formula in my sheet I have the same error message.
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.
1 best response

Accepted Solutions
best response confirmed by Yamina Azri (Copper Contributor)
Solution

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)

 

 

 

View solution in original post