Excel Formula

A

ATK

I am looking for a formula that will allow me to apply one multiplier up
to a certain sum and then a different multiplier after it hits that
limit.

For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of
the numbers until 10, I need to apply a multiplier of 2. All sums after
10, I need to apply a multiplier of 10.

In this case, I would like the responding column to read 2 (2*1),
6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10)

Thanks.
 
B

Bob Phillips

Here is a shot

=IF(A1*2>10,A1*10,A1*2)

it doesn't work for the 7 in your example, but that one doesn't seem to
correspond to your description.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

swatsp0p

=IF(SUM($A$1:A1)<10,A1*2,A1*10)

Assumes your data is in column A1:Axx, formula entered in B1 and copied
down. I am guessing you want to sum the range from A1 down to the cell
containing the active formula. e.g. in cell B5, the formula will
read:

=IF(SUM($A$1:A5)<10,A5*2,A5*10) and with your sample data will return:
100

However, B4 will return 70, not 62 (2*1+6*10), (how did you arrive at
that with a vaule of 7?)

Is this what you are looking for?
 
R

Roger Govier

Hi

Your description doesn't match your example.
Going by the example, I guess you multiply by 2 for any value up to and
equal to 5,
any value between 6 and 9, is 6*10 plus (Value-6)*2 and any value of 10
and higher multiply by 10.
If so
=IF(A1>=10,A1*10,IF(A1>=6,(6*10+(A1-6)*2),A1*2))

Regards

Roger Govier
 
R

Roger Govier

Hi

Your description doesn't match your example.
Going by the example, I guess you multiply by 2 for any value up to and
equal to 5,
any value between 6 and 9, is 6*10 plus (Value-6)*2 and any value of 10
and higher multiply by 10.
If so
=IF(A1>=10,A1*10,IF(A1>=6,(6*10+(A1-6)*2),A1*2))

Regards

Roger Govier
 
A

ATK

I guess my description was a little ambiguous. When I say sum of th
numbers, I mean the sum of the numbers in the column. So, for th
resulting value of 62, the sum of the first three numbers is 9. Th
forth number in the column is 7. So, 1 gets the multiplier of 2 and
gets a multiplier of 10 --> 2*1+6*10=62.

Make more sense now
 
Top