Comparing 3 Columns in Excel

[Idris]

Dear All,

I need help to compare three col and show the value in column Result

Result Col contains formula as:
IF(E92>=F92, IF(D92<0,0,IF(D92>E92,E92-F92,D92-F92)),IF(D92<0,F92*-1,IF(E92<0, F92*-1,E92-F92)))

-------D------E------F---Result--Balance
90.....100......75......50.......25.......75
91......50......75......100......-25......50 <------- correct fig should be -50
92......75......50......100......-50......50 -------- in result column
93......75......100......50......25.......75 --------- rest all the figures
94......50......100......75......-25......50 -------- the figures in result col
95......100......50......75......-25......50 -------- are correct

Please help - thanks

 

[stopher]

Hi

Could you explain the rule why line 91 should be -50.

As I see it for line 91:

E<F and D>=0 and E>=0 so the result is the very last part of your equation:
IF(E92>=F92, IF(D92<0,0,IF(D92>E92,E92-F92,D92-F92)),IF(D92<0,F92*-1,IF(E92<0, F92*-1,E92-F92)))

So E-F= -25

Chris

 

[Idris]

Hi

D col represent customer actual balance
E col represent 60/90/120 days/ageing col balance
F col represent carried forward provision

Result col is new value of provision required

If D col balance is 50 and E col balance is 75
provision amt is 100 - so you need provision thats equal to 75 or 50 which ever is less - hence 50-100 = -50

hope i am able to explain!

 

[stopher]

Take a look at this:

MIN(MAX(D92,0),MAX(E92,0))-F92

The point being that if either E or F are less than zero then you want to consider them as zero - you can do this using MAX(E,0) and MAX(F,0).

Then you can use MIN to determin the lesser of D & E which you can subtact F from.

But the only condition this doesn't deal with is the case when you want the result to be zero (where D<0 and E>=F), so you need to use an IF statement:


IF(AND(D6<0,E6>F6),0,MIN(MAX(D6,0),MAX(E6,0))-F6)


hth

Chris

 

[Idris]

Thankyou - Very much



Last night I started working from scratch and

=IF(D6<=0, F6*-1, IF(E6+F6<=0,0,MIN(D6:E6)-F6))

but still the result werent so accurate

Thanks a million.