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 Friends,

Sorry, there was little mistake in the conditions, am modifying that


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 pls help me friends.

Regards
RAMU

 

[jasonb75]

What about the other possibilities?

A2 =4500 & D2 <4, A2 =4500 & D2 >4, A2 <4500 & D2 =4, A2 >4500 & D2 =4

 

[RAMU]

What about the other possibilities?

A2 =4500 & D2 <4, A2 =4500 & D2 >4, A2 <4500 & D2 =4, A2 >4500 & D2 =4

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

 

[RAMU]

Dear Friends,

Pls help me.

Regards
RAMU

 

[jasonb75]

Does D2 hold a % value (does it show the % symbol in the cell)?

 

[RAMU]

Dear Jason,

No, it shows only number like 1 or 3 or 10 etc.

Regards
RAMU

 

[jasonb75]

Based on what you've asked for try

=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/100)),MIN(B2:C2)-(MIN(B2:C2)*((D2-4)/100)))),0)

 

[RAMU]

Dear Jason,

Perfect, it works fine. I just modified little bit bcoz I had to change the condition.
However, it works gr8.

But one thing, seriously I couldn't understand how it works..

Thanks
RAMU

 

[jasonb75]

It works on process of elimintation, not really sure how to explain it to be honest,

I did miss some common factors in the results before, this should give the same results.

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

Note that with the second part, a false result on D2>4 would subtract 0 from the first part, making it suitable for the criteria where there should be no subtraction.

Likewise /4500*min(4500,a2) would result in /4500*4500 where the result should just return the lower value, this means the result is unchanged.