if formulas

[JohnBAH]

Im trying to write a formula that calculates progressive values from a single input. If I input Sales in cell A1 I want cell A2 to return the value in A1 if it is <= A1. I then want to set parameters at say 1,000,000 breakpoints for cells A2 - A6. So if Sales in A1 were 3,500,000 then A2 = 1,000,000, A3 = 1,000,000, A4 = 1,000,000, A5 = 500,000, A6 and beyond would be Zero. Can someone help? Thanks.

 

[lenze]

Hi and welcome to the board!!
In A1, use

=MIN(1000000,A1)

In A3, use

=MAX(MIN($A$1-SUM($A$2:$A2),1000000))

Copy A3 down as needed
Sorry, I didn't use an IF. Hope that's not a problem!!

lenze

 

[JohnBAH]

Lenze, thanks but I think I may have asked the ? wrong. How would I do this in columns vs. down a single column? I tried to transpose the formula but when I enter $3,500,000 in A1 I keep getting $1,000,000 as a value in every successive column? Sorry

 

[JohnBAH]

Lenze, thanks but I think I may have asked the ? wrong. How would I do this in columns vs. down a single column? I tried to transpose the formula but when I enter $3,500,000 in A1 I keep getting $1,000,000 as a value in every successive column? Sorry

 

[lenze]

The Formula in B1 would be the same as I used in A2,The Formula in C1 would be

=MAX(MIN($A$1-SUM($B$1:B$1),1000000),0)

drug across. Note the 2nd formula in 1st post is missing a 0, Should be

=MAX(MIN($A$1-SUM($A$2:$A2),1000000),0)

lenze

 

[JohnBAH]

Lenze, thank you very much