now I have that working trying another formula

I have two cell working with previous formula with the 2 amounts capped.



I then want a total of the 2 amounts but if both cells have been capped I want the cap figure and not the SUM of the 2 cells.

I currently have this =SUM(T9,W9) and tried this =SUM(T9,W9,J9) but with this it caps all and doesn't add the 2 cells when needed.

any ideas please


Edit mod : this post is related to https://www.msofficeforums.com/excel...l-formula.html

Which of the capped amounts would be the result?
Post a small sample sheet with some data and expected results ( click Go advanced - Manage attachments)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

the capped figure would be the J9 cell

Add an example as requested please

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

please help

Attached Files

MSExcel.xlsx (10.0 KB, 13 views)

Perhaps =MIN(L7+O7,C7) ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This works OK but if a cell in the calculation L7+O7 is blank it returns #value??

I still want it to return the value of cell C7

What about?


=MIN(C7,SUM(L7,O7))

NBVC Brilliant - works well thank you

If it returns a VALUE error, it is not blank but contains the empty text string ""

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Looking some help.

I am looking column N to be Column J * M, if more than column B enter column B's value minus column L. In red what is should be.


ABCDEFGHIJKLMNOP30/01/201850.00YES11.1yes06/03/201822/05/201850.00£1.0026£26.0056£56.00 (£24.00)82£50.00

Try:


=IF(J2*M2>B2,B2-L2,J2*M2)