Excel Formulas

[thifu3]

for example

A B C

3 9 5


D=A+B+C=17

I want to make highest value of D is 8 and if exceeds, then it should go to like cell E


D=8 and the rest 9 in cell E


please help

 

[manish.agrawal446@gmail.com]

Hi,

Pls put IF((F43+F44+F45)>8,8,F43+F44+F45) in d where f43,f44 and f45 contains value of A,B,C

And in E put the formula

IF(F46=8,F43+F44+F45-8,"")

 

[xld]

Use


=MIN(8,SUM(A2:C2))


and


=SUM(A2:C2)-D2

 

[kuldeepjainesl]

Hi Thifu,


Assuming your data in


A1=3

B1=5

C1=9


Put formula in


D1=IF(SUM(A1:C1)<=8,SUM(A1:C1),8)

E1=IF(D1=8,(SUM(A1:C1)-D1),0)


But i think XLD approach is better. Shorter and smarter...


Regards,