next to impossible

  • Thread starter IF function incompetent
  • Start date
I

IF function incompetent

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!
 
J

JE McGimpsey

One way, if I understand you correctly:

In your "separate workbook", use Insert/Name/Define to assign a name to
the table in the other workbook (say "table"). Then

=INDEX(table, 1, MATCH(E2, OFFSET(table, MATCH(F2,
OFFSET(table,,,,1), FALSE)-1,,1), TRUE))


IF function incompetent
 
J

Jim

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
 
I

IF function incompetent

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??
 
J

Jim

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))
 
I

IF function incompetent

=(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 said:
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 said:
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??
Click to expand...
Click to expand...
 
I

IF function incompetent

I tried yours too but I just can't get it right.

JE McGimpsey said:
One way, if I understand you correctly:

In your "separate workbook", use Insert/Name/Define to assign a name to
the table in the other workbook (say "table"). Then

=INDEX(table, 1, MATCH(E2, OFFSET(table, MATCH(F2,
OFFSET(table,,,,1), FALSE)-1,,1), TRUE))


IF function incompetent
Click to expand...
 
J

Jim

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 said:
=(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 said:
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 said:
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??

:

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



:

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!
Click to expand...
Click to expand...
Click to expand...
 
P

Pete_UK

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

=(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 said:
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]She­et1!$A$1:$F$21,4,FALSE))
Click to expand...

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??
:
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
:
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!- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
I

IF function incompetent

Nothing is in F2 and G2. Grade levels from 10 down to 36 are in Col A. How
do I make sure they are all numbers?

Jim said:
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 said:
=(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 said:
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))



:

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??

:

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



:

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!
Click to expand...
Click to expand...
Click to expand...
 
I

IF function incompetent

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

Pete_UK said:
You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

=(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 said:
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]She­et1!$A$1:$F$21,4,FALSE))
Click to expand...

"IF function incompetent" wrote:
Click to expand...
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??
Click to expand...
"Jim" wrote:
Click to expand...
I put your data in A4:F24, pay grade in E2, pay rate in F2 and the following
in G2:

Format as %

"IF function incompetent" wrote:
Click to expand...
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
Click to expand...
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)
Click to expand...
Pay Grd 80% 90% 100% 110% 120%
Mid Point
Click to expand...
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
Click to expand...
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)
Click to expand...
Please help!! I am so lost!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
P

Pete_UK

In that case omit everything inside the square brackets (and the
square brackets themselves), but make sure that where you have Sheet1
you use the actual name of the sheet where the table is located. It
might be easier to set up a named range for your table.

Pete

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.



Pete_UK said:
You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.
Click to expand...
Hope this helps.
Pete
Click to expand...
=(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 :(
:
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]She­­et1!$A$1:$F$21,4,FALSE))
:
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??
:
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
:
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 formulafor)
Please help!! I am so lost!- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
J

Jim

In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

IF function incompetent said:
oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

Pete_UK said:
You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
I

IF function incompetent

I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

Jim said:
In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

IF function incompetent said:
oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

Pete_UK said:
You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
J

Jim

Is G2 = 0?

IF function incompetent said:
I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

Jim said:
In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

IF function incompetent said:
oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

:

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
I

IF function incompetent

Yes it does now

Jim said:
Is G2 = 0?

IF function incompetent said:
I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

Jim said:
In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

:

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

:

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
J

Jim

G2 should be a value relating to the grade in F2.

IF function incompetent said:
Yes it does now

Jim said:
Is G2 = 0?

IF function incompetent said:
I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

:

In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

:

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

:

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
I

IF function incompetent

no it just says 0 and here is the formula I used from you:

=(1+(G2-VLOOKUP(F2,'Pay Grid'!A4:F24,4,FALSE))/VLOOKUP('EE Pay
grade'!F2,'Pay Grid'!A4:F24,4,FALSE))

Jim said:
G2 should be a value relating to the grade in F2.

IF function incompetent said:
Yes it does now

Jim said:
Is G2 = 0?

:

I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

:

In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

:

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

:

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
I

IF function incompetent

It also said something about a circular reference

IF function incompetent said:
no it just says 0 and here is the formula I used from you:

=(1+(G2-VLOOKUP(F2,'Pay Grid'!A4:F24,4,FALSE))/VLOOKUP('EE Pay
grade'!F2,'Pay Grid'!A4:F24,4,FALSE))

Jim said:
G2 should be a value relating to the grade in F2.

IF function incompetent said:
Yes it does now

:

Is G2 = 0?

:

I think that I should be called Excel incompetant period. Is there any way
that I can attach my spreadsheet to you and see where I have gone wrong. Now
I have 1.86417E-5 as my answer. so sorry for being such a pain

:

In that case, with the table in sheet1 and grade in F2, sheet 2, rate G2,
sheet 2 put this in H2 sheet 2:

=(1+(G2-VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))/VLOOKUP(F2,Sheet1!$A$1:$F$21,4,FALSE))

However, if F2 contains a '10 for instance and col A in sheet 1 has 10 you
will still get #NA. Also, if col A in sheet one has '10 and F2 has 10 you
will get the same result. F2 and col A both have to be either numbers or
text.

Jim

:

oh dear, I think I may have said it wrong to you guys :( sorry, the info is
in the same file or work book just on a different tab or sheet.

:

You will have to have the full path and the .xls after the filename if
the other workbook is not open. If it is open, then you will need
[filename.xls] between the square brackets.

Hope this helps.

Pete

On Feb 27, 4:09 pm, IF function incompetent
=(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 :(



:
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]She­et1!$A$1:$F$21,4,FALSE))

:

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??

:

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

:

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!- Hide quoted text -

- Show quoted text -
Click to expand...
Click to expand...
Click to expand...
 
Top