Excel Formula
 

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.


--
ATK
------------------------------------------------------------------------
ATK's Profile: http://www.excelforum.com/member.php...o&userid=27662
View this thread: http://www.excelforum.com/showthread...hreadid=476252

Here is a shot

=IF(A1*210,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)


"ATK" wrote in message
...

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.


--
ATK
------------------------------------------------------------------------
ATK's Profile:
http://www.excelforum.com/member.php...o&userid=27662
View this thread: http://www.excelforum.com/showthread...hreadid=476252

=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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=476252

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



ATK wrote:

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.

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



ATK wrote:

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.

Excel Formula
 

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

Make more sense now?


--
ATK
------------------------------------------------------------------------
ATK's Profile: http://www.excelforum.com/member.php...o&userid=27662
View this thread: http://www.excelforum.com/showthread...hreadid=476252