condition formula

[RAMU]

Dear Friends,

Am trying to solve a formula for last few hours but yet to solve.

A B C D E
1 GCV CQ RQ MOIS GRN QTY
2 4500 4


IF A2 =4500 & D2=4, THEN E2 WILL BE LOWER QTY IN BETWEEN B2 & C2
IF A2 >4500 & D2>4, THEN E2 WILL BE (LOWER QTY IN BETWEEN B2 & C2)-(LOWER QTY IN BETWEEN B2 & C2*(D2-4))
IF A2 >4500 & D2<4, THEN E2 WILL BE LOWER QTY IN BETWEEN B2 & C2
IF A2 <4500 & D2>4, THEN E2 WILL BE( (LOWER QTY IN BETWEEN B2 & C2/4500*A2)-(LOWER QTY IN BETWEEN B2 & C2*(D2-4)))
IF A2 <4500 & D2<4, THEN E2 WILL BE (LOWER QTY IN BETWEEN B2 & C2/4500*A2)

BUT IN CASE B2 & C2 IS EQUAL, THEN IT WILL TAKE ANY VALUE IN BETWEEN B2 & C2,BUT OTHER CONDITIONS REMAIN SAME.

RESULTED FIGURE MUST BE A ROUND FIGURE.

Pls help me Friends.

Thanks in advance.

Regards
RAMU

 

[RAMU]

Dear Jason,

Sorry same result is not coming friend.

earlier one is giving right one i.e.75 for this condition:
IF A2 <4500 & D2>4, THEN E2 WILL BE( (LOWER QTY IN BETWEEN B2 & C2/4500*A2)-(LOWER QTY IN BETWEEN B2 & C2*((D2-4)%)))

though I had to modify little bit your earlier one.
Modified formula is: =ROUND(IF(D2<=4,IF(A2>=4500,MIN(B2:C2),MIN(B2:C2)/4500*A2),CHOOSE(SIGN(A2-4500)+2,(MIN(B2:C2)/4500*A2)-(MIN(B2:C2)*((D2-4)/100)),MIN(B2:C2)-(MIN(B2:C2)*((D2-4)/100)),MIN(B2:C2)-(MIN(B2:C2)*((D2-4)/100)))),0)

whereas last one is giving 12. Pls help

Regards
RAMU

 

[jasonb75]

edit:-

Just found the errors, try

=ROUND(MIN(B2:C2)/4500*MIN(4500,A2)-IF(D2>4,MIN(B2:C2)/4500*MIN(4500,A2)*(D2-IF(A2<>4500,4))%),0)

 

[RAMU]

Dear Jason,

Suppose A2=3000, B2=95, C2=96, & D2=10

Then the earlier one is giving result 58.
But the last one is giving 60.

earlier one is right.

Pls help

Regards
RAMU

 

[jasonb75]

Think i got it this time

=ROUND(MIN(B2:C2)/4500*MIN(4500,A2)-IF(D2>4,MIN(B2:C2)*(D2-IF(A2<>4500,4))%),0)

 

[RAMU]

Dear Jason,

Suppose A2=4500, B2=95, C2=96, & D2=10

Then the earliest one is giving result 89.
But the last one is giving 86.

earliest one is right.

Pls help

Regards
RAMU

 

[jasonb75]

I was still working with the criteria from your earlier posts and overlooked one of the changes you made.

=ROUND(MIN(B2:C2)/4500*MIN(4500,A2)-IF(D2>4,MIN(B2:C2)*(D2-4)%),0)