Formula for total to stop at 0

[Kitkat1229]

Hi All, I am distributing items across store locations and am having trouble with my distribution final total. Stores are ranked by a, b, and c. I put how many I need to be distributed out in these columns. For example, the items in row 11 have a total of 10,000 in stock to distribute. I used the SUM and ABS formulas to help count this total, but my problem is that if I go over 10,000 it will not stop at 0, but proceed to count up. Does anyone know a formula to have this total stop at 0? Help is much appreciated!

[TMS]

No entirely sure where the formula is. But you can probably use MAX. For example

Please Login or Register  to view this content.

[Kitkat1229]

Hi, the formula is hidden off in the AX column for each total. I tried putting the formula =MAX(10000,SUM(L11:AS11)*1) in AU11, but when I play around with the distro numbers in line 11, it still counts past 10000. Am I not typing the formula correctly?

[TMS]

Sorry. I think it should be

Please Login or Register  to view this content.

Why do you have *1? It doesn't do anything.

[Kitkat1229]

Hi TMS,

I had this in another equation to counteract some negatives. I have taken it off. Thanks for this. The min made the count across stores store, but did not keep the distro list from going over the qty of 10,000 across the different store locations. Do you think that I need to change my IF conditional equations? Thanks!

[davsth]

how does *1 counter negatives?

I think you need to restate your problem and say the formulas that are giving you issues. And how you would wish to resolve this

Is the sheet populated left to right and when you reach 10000 you do not want the formulas to return values

=IF(L$9="a",$H11,IF(L$9="b",$I11,IF(L$9="c",$J11,IF(L$9="d",$E11,IF(L$9="","")))))

can be =HLOOKUP(L9,$H$9:$K$11,3,0) in l11
in m11 and across =IF(SUM($L11:L11)+HLOOKUP(M9,$H$9:$K$11,3,0)>10000,10000-SUM($L11:L11),HLOOKUP(M9,$H$9:$K$11,3,0))

but this is guesses you should really explain!

[kvsrinivasamurthy]

I am not clear about Problem.
i guess the total of distribution should not exceed the limit 10,000.
Try this. In L11 then copied to full range.

Please Login or Register  to view this content.