• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get the correct percentage of a scale of numbers

lirguio

lirguio

New Member
Hi All,

I am trying to get the correct % for an amount achived at some point of the year.

The scale is as follow:

Value Percentage
40,000 1%
35,000 2%
30,000 3%
10,000 4%
5,000 5%

At the end of the period the result value is 18,000 the percentage achived should be 3.6%
but if the value achive was 33,000 the percentage should be 2.4%
How can I build a formula that get the results everytime I place a number between the range 5,000 to 40,000.

Many thanks in advance for your help,
as always you are super stars

Leonardo
 
Hi lirguio,

Try this:

=FORECAST(C1,B1:B5,A1:A5)

Your data in colmn a & b and C1 contains value for forecast
 
Hi Faseeh,

Thanks for your quick reply, I didn't come accross this formula before, it's a great help, but it would not work for amounts like 29,000 the result value of the formula is 2.51% when I was expecting 3.05%. I believe it's because the changes in the scale are not the same value between them.

Do you have other suggestion to integrate in the Forecast formula?
Thanks a lot for looking into my issue.

Cheers,

Leonardo
 
@lirguio

Just see the attached file. It is working on the range of 5000-40000, if you say the range will change just try by changing this range and see if the desired result is achieved.

Formula used is:

=LOOKUP(D4,$A$2:$A$7,$B$2:$B$7)-(D4-INDEX($A$2:$A$7,MATCH(D4,$A$2:$A$7)))/(INDEX($A$2:$A$7,MATCH(D4,$A$2:$A$7)+1)-INDEX($A$2:$A$7,MATCH(D4,$A$2:$A$7)))/100

Regards,
 

Attachments

  • Leonardo_Chandoo.xlsx
    9.3 KB · Views: 11
Hi Somendra,
That's excellent, thank you very much. I was doing some work with Idex and match formulas, but this is by far outstanding expertise.

Thanks a million,

Kind regards,

Leonardo
 
One more approach using TREND function.
Code: 
=TREND(INDEX($B$2:$B$7,MATCH(D4,$A$2:$A$7,1)):INDEX($B$2:$B$7,MATCH(D4,$A$2:$A$7,1)+1),INDEX($A$2:$A$7,MATCH(D4,$A$2:$A$7,1)):INDEX($A$2:$A$7,MATCH(D4,$A$2:$A$7,1)+1),D4)
 
Hi Shivallabha,

Thank you very much, Yes, your formula works as well.
You as well have a great knowledge of excel formulas.
Regards,

Leonardo
 
Hi All,

Just one more to add:

If you can sort this table for values in Col A (with zero added like in Somandra's book), you can use this:

=FORECAST(C1,OFFSET(INDIRECT("B"&6-LOOKUP(E1,A1:A6,ROW(A1:A6))),,,-2,1),OFFSET(INDIRECT("A"&6-LOOKUP(E1,A1:A6,ROW(A1:A6))),,,-2,1))

If don't sort, try this:

=FORECAST(C1,OFFSET(INDIRECT("B"&6-LOOKUP(E1,(SMALL(A1:A6,ROW(B1:B6))),ROW(A1:A6))),,,-2,1),OFFSET(INDIRECT("A"&6-LOOKUP(E1,(SMALL(A1:A6,ROW(B1:B6))),ROW(A1:A6))),,,-2,1))

..with CSE.
 
@lirguio

I just realised that your data has a decrease of 1% in each range and if the percentage is change to different value like 5000 - 5%, 10000 - 4%, 30000 - 3.5%, 35000 - 2%, 40000 - 1%

Then my formula will not give accurate result. So try this modified formula.

=LOOKUP(D3,$A$1:$A$6,$B$1:$B$6)-(((INDEX($B$1:$B$6,MATCH(D3,$A$1:$A$6))-INDEX($B$1:$B$6,MATCH(D3,$A$1:$A$6)+1))*100)*(D3-INDEX($A$1:$A$6,MATCH(D3,$A$1:$A$6)))/(INDEX($A$1:$A$6,MATCH(D3,$A$1:$A$6)+1)-INDEX($A$1:$A$6,MATCH(D3,$A$1:$A$6))))/100

Red portion is added to take care of that.

@shrivallabha formula will take care of such cases.

@Faseeh formula I could not test as I could not trace use of ref. E1 in lookup formula.

Just try this case.

Regards,
 
Another possibility...

=PERCENTILE(B$2:B$6,1-PERCENTRANK(A$2:A$6,C2,20))

lookup value: C2. lookup range: A2:B6.
Note: assumes decreasing data. (If data is increasing remove the "1-")
 
I have a related query, but can't extract anything workable from the above. I am trying to use a chart that goes from 0-100% for BMI. To make it align 0-25% would be 0-18.5, 25-50% is 18.5-24.9 and so on. As far as I can tell the highest value is 56, so 75-100% is 30-56.

BMI
Below 18.5
18.5 – 24.9
25.0 – 29.9
30.0 and Above

So how can you cope with these very different spreads?
 
Hi Seahorse,

Try posting a new thread to attaract a quicker solution for youe problem, even though the problem look similar.

And regarding your question, don't you think the distributing an even percentage is wrong, I Mean you should see what %age is 18.5 of 56 and so on. Try that way, may be you get answer.

Regards,
 
You must log in or register to reply here.
Back
Top