WHAT FORMULA DO I USE? I want to use Microsoft Excel to answer the the following equation:?

Question

WHAT FORMULA DO I USE? I want to use Microsoft Excel to answer the the following equation:?

5.25% of initial 25
3.25% of remaining

for 5 values 5, 10, 20, 50, 150

so i want it to figure out whether or not each value is more than 25 if it is i want it to calculate 5.25% of 25 and 3.25% of remaining value

Answers should be:

5 = 0.2625
10 = 0.525
20 = 1.05
50 = 2.125
150 = 5.375

i would just like to enter ANY value, not necessarily one of the 5 given values, (i.e. "50") and see results (i.e. "2.125"). i would like to enter any number value i want excel to calculate the rest. thanks in advance

Answer 1

I think what you're looking for is:

10 -> 10(0.525)
30 -> 25(0.525) + 10(0.325)

If that's correct, then use this:

Say you're putting the input values (5, 10, etc) in box A1.
Then in box B1, use the IF function:
IF(condition, true, false)

IF(A1<25, A1*0.525, (A1-25)*0.325 + 25*0.525)

Answer 2

So I think what you mean to say is that you want to calculate 5.25% of the first 25 values, and for any values after that point, you want to calculate 3.25% of the remaining values.

I'm assuming that you would enter your values in column A, and expect the answers to return in column B. In that case, enter your first value in A1, and enter this formula in B1:

=if(count(A$1:A1)<=25, 0.0525*A1, 0.0325*A1)

You should be able to Copy-Paste this down the cells of column B, then enter your values in column A, and excel will calculate the correct percentage for you.

It is important that you copy paste the cells in column B, and not type it in manually, because excel will automatically change "A1" to "A2" for the second row, and "A3" for the 3rd row, so to save typing.


[edit] This answer was given before the submitter amended his or her question to be less ambiguous.

Answer 3

You can use the following:

= if (A1<=25, 5.25% * A1, 5.25% * 25 + 3.25% * (A1 -25) )

The explanation is as follows:

If the cell with the value (I used A1) is <=25 , then use condition1, which is multiply the value times 5.25%,

otherwise use condition2, which multiplies the first 25 times 5.25%, then adds the remainder (A1 - 25) multiplied by 3.25%.

Note: In Excel the conditions are separated by commas.

I checked it in Excel and it works!

Answer 4

Heather8875's answer is correct with the following exceptions:
The beginning of the equation needs a "=" sign and the decimal places are all in the wrong spot. Try this:

=IF(A1<=25, A1*0.0525, (A1-25)*0.0325 + 25*0.0525) or
=IF(A1<=25, A1*0.0525, (A1-25)*0.0325 + 1.3125) since 25*5.25% is always the same, you don't really need to figure it out everytime.
I know you're not talking about counting the entries because you gave examples of the answers you expect in the question.

Answer 5

I'd give the 10 pts to heather; she suffered thru the ambiguity enough, EVEN before some follow-up editing.

You don't make it clear whether the initial 25 is the first 25 data points or whether it's the first 25 value (if there) in each data point. Your data pt of 50 leads one(me) to presume you mean the latter.

In Column A's cells input your data points, one data point per cell; in column B use heather's "If" on the adjacent left cell; then copy that "IF" down column B's remaining cells that are adjacent to any filled in data cells(in column A).

...and as richj astutely pointed out, heather's conversion of percents to decimals need some "refinement" (that she and I very carelessly overlooked).

Answer 6

(cryp3) code in not working well

i.e
if 300, cryp3 code answer is 15.75
and the right answer is 10.25


10 POINT GOES TO (heather8875)

I tired ( heather8875 ) Code
Its Nice and good
but i done some fix (0.525 to 0.0525, from %50 to %5)

=IF(A1<25, A1*0.0525, (A1-25)*0.0325 + 25*0.0525)

now, Its right and work %100

Answer 7

=IF(G2>G3,G2*5.25%,"Less then 25") where G2 is the number you want to test, and G3 is the limit (ie, 25 in this case) This will display 5.25% of G2 if it is over G3, and will display "Less than 25" if not. Then =B4*3.25% Where B4 is the cell that the first equation is located in This will multiply the value, if any, by 3.25% That should do ya.