Excel question?

Question

Can someone explain ratio to me? I have done a =sum(a1*10%)+a1 to get a 10 percent increase on a row but my boss wants the percent to decrease with the amount so maybe 20% of 100 but only 10% of 1000. Does this make sense????

Answer 1

It makes sense. If there is no any particular pattern you want it in, you should do the following.

Price range =1000-100 =900
Rate range =20%-10%=10%

So for every dollar, rate changes (decreases) by 10%/900 = 0.0001111

So your formula should be
=IF(A1-100>0;20%-(A1-100)*(0.1/900);20%)

A1 is the price or the amount.
What i have shown is how to get the rate proportionately.

You enter some random nos starting from 100 & ending on 1000. Type this formula in the next column & see. It decreases the rate as the amount/price increases.
(Move the mouse over the formula so you can see it in full)

Answer 2

only vague sense

boss man hasn't given you enough information

ask him the following:


1. how far out should the figures go?

example -- you used 100 and 1,000 ... does he want to go out to 1,000 only? if not, how far??

2. then you set down a series of points between the figures you know [100 and 1000 in your example] and ask him what percentage increases he'll like at each point along the way.

example:

100, 125, 155, 195, 245, 305, 380, 475, 595, 745, 930

maybe he'll put down the following:

20%, 19%, 18%, 17%, 16%, 15%, 14%, 13%, 12%, 11%, 10%

you know your boss (I don't) so if this is 'too much' data for him, only give him 3 to 5 of the points [well spaced out] and then let him respond.

***
one general equation which will yield approximately similar results is a = a1 * (1 + 1/10 + (1000-a1)/90 * 1/100)

in this eqn, your new value a will be equal to 110% of the old value a1 when a1 = 1000. If a1 was 100, the new value will be 20% higher [or 120 in all].

however when a1 is less than 1000, [my example 400] the difference between it and 1000 [in my example, 600] is divided by 90 [in my example, yielding 6.67] and then by 100 [yielding 0.0667] -- which is added to the 10% increase giving a total increase of 16.67% [final answer 467]

the general equation simplifies to
a = a1 * (1.1 + (1000 - a1) / 9000 )

{I did not prevent the percentage increase from becoming less than 10%. If the old value becomes as high as 1900, the calculated increase would be zero.}

***
or, you could simply give him the points
100, 400, 1000, 1900

and ask him if these percentage increases will suit
20%, 16.67%, 10%, 0

if he agrees, just use the equation I provided.



does this help?

Answer 3

You need to use an if statement to do what you want.

Let's say you have a spreadsheet full of products and you want ones that sell for less than $100 to be automatically increased by 10%.

What you need is an IF statement that allows you to apply the 10% increase based on the criteria.
=IF(B3<100,(B3+(B3*0.1)),B3)

This IF statement tells Excel "If the value in B3 is less than 100, give me what's in B3 plus 10% of B3. Otherwise, just give me what's in B3, as is."

I think this explains everything you asked.

Answer 4

You have to use absolute cell reference to do this.
If you could write the question again and tell me what cell you have etc I might be able to answer the question for you.