I have a rating system which is the following:
10 = $5
9 = $10
8 = $15
7 = $20
6 = $25
5 = $30
4 = $35
3 = $40
2 = $45
1= $50
The following value is input in a cell: $18.07
I would like to create a formula that automatically calculates the appropriate rating to the first decimal such as (1.2,4.7,etc.)
Any ideas? I tried LOOKUP but they only accept ascending ordered numbers and they don't calculate to the first decimal.
Try this out...
=11-(A2/5)
Where A2 is the inputted money.
To round it to the tenths place, just change number formatting to show only one place past the decimal point.
starryknight64
Mind telling me how you got that? lol. I knew there was some easy formula like that but didn't know how to come up with it.
Sure thing!
It's basic algebra. The number on the left side, we'll call X. The number on the right we'll call Y.10 = $5
9 = $10
8 = $15
7 = $20
6 = $25
5 = $30
4 = $35
3 = $40
2 = $45
1 = $50
(10,5)....
How do I get from 10 to 5? Divide 10 by 2!
10/2=5! Correct
So...X/2=Y? Is this true?
Lets check the next ordered pair (9,10)...
9/2=10? Incorrect. There's something missing...
Look at the progression of Y's...5,10,15,20...see a pattern?
They're multiples of 5...so, if I multiply 5 by some constant C minus X, I'll get Y.
What is constant C though?
So far, I have 5 * ( C - X ) = Y
Well, look at the first ordered pair again: (10, 5)
X=10
Y=5
What is C?
5 * ( C - 10 ) = 5
Divide by 5 on both sides
C-10=1
Add 10 to both sides
C=11
WAH LA
5(11-X)=Y
And, from this we get
X=11-(Y/5)
QED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks