Excel Formula

[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.

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

[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)


"ATK" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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



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.
>
>
>
>

[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



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.
>
>
>
>

[ATK]

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?