Round number down if exceeds x

[Excelnoob124]

Hi guys first post here I am struggling to find a method to set cell equation to max of 100.

Formula : =IF(F3="W",(E3-B3),0)
Explanation: The answer from E3-B3 can vary from 1 to 200+ but I want it to display a maximum ammount as 100 .
I cannot validate "E or B " as that is a separate thing .

I hope this makes sense , all I want is the If statement to workout E-B and not exceed an ammount.

[modytrane]

Try:

=IF(F3="W",max((E3-B3),100),0)

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

[Pepe Le Mokko]

@mody
you need the MIN function here, not the MAX function

[modytrane]

sorry, Pepe is right. It should be MIN in the formula.

[Excelnoob124]

Thank you very much Pepe!
I tried Max before asking question here and it never worked,
I would have never though MIN would do the trick.
Thank you again

[Pepe Le Mokko]

You're welcome