next to impossible
What's in F2, G2 and Col A in PayGrid? Are they all numbers? Or perhaps some
are text that look like numbers?
The formula should find the mid point for a pay grade and then calculate the
difference between the actual pay rate and the mid point for that pay grade
and express that diffierence as a percent of the mid point.
Works for me if F2, G2 and col A in the table are all numbers.
GL
Jim
"IF function incompetent" wrote:
=(1+(F2-VLOOKUP(E2,[PayGrid]Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(E2,[PayGrid]Sheet1!$A$1:$F$21,4,FALSE))
THis is what I did and it just says NA :(
"Jim" wrote:
It's not necessary - you can use two workbooks. With two workbooks, the
formula for G2 would be:
=(1+(F2-VLOOKUP(E2,[Book1]Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(E2,[Book1]Sheet1!$A$1:$F$21,4,FALSE))
"IF function incompetent" wrote:
ok so, I tried to do what you said and it didn't seem to work out, so I moved
the table to the same workbook as where the other information is. Maybe this
will help??
"Jim" wrote:
I put your data in A4:F24, pay grade in E2, pay rate in F2 and the following
in G2:
=(1+(F2-VLOOKUP(E2,$A$4:$F$24,4,FALSE))/VLOOKUP(E2,$A$4:$F$24,4,FALSE))
Format as %
HTH
JIm
"IF function incompetent" wrote:
I am trying to find the percentage at which the pay rate is equal to what
percentage based on a two factors 1) what pay grade it falls into and 2)
where it falls between 80% and 120% from the table
The info that I need to get this from is in a workbook on its own. starts
from A1 and ends at F24 (looks like this)
Pay Grd 80% 90% 100% 110% 120%
Mid Point
10 20,790 23,388 25,987 28,586 31,184
11 25,848 29,079 32,310 35,541 38,772
12 27,360 30,780 34,200 37,620 41,040
13 29,392 33,066 36,740 40,414 44,088
14 32,056 36,063 40,070 44,077 48,084
15 35,088 39,474 43,860 48,246 52,632
16 38,760 43,605 48,450 53,295 58,140
17 43,376 48,798 54,220 59,642 65,064
18 49,024 55,152 61,280 67,408 73,536
19 54,600 61,425 68,250 75,075 81,900
26 40,760 45,855 50,950 56,045 61,140
27 45,696 51,408 57,120 62,832 68,544
28 51,816 58,293 64,770 71,247 77,724
29 59,296 66,708 74,120 81,532 88,944
30 67,480 75,915 84,350 92,785 101,220
31 77,000 86,625 96,250 105,875 115,500
32 87,928 98,919 109,910 120,901 131,892
33 109,368 123,039 136,710 150,381 164,052
34 134,523 151,338 168,153 184,969 201,784
35 147,974 166,471 184,968 203,465 221,962
36 162,771 183,118 203,464 223,810 244,157
In a separate work book:
Cell E2 = annual base rate
Cell F2 = Pay Grade
***Cell G2 = % (this is what I am trying to find the formula for)
Please help!! I am so lost!
|