Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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)) In article , 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
=(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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
I tried yours too but I just can't get it right.
"JE McGimpsey" wrote: 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)) In article , 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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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 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]She*et1!$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!- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
In article ,
IF function incompetent wrote: I tried yours too but I just can't get it right. It would be helpful if you fleshed out "just can't get it right"... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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" wrote: 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! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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 On Feb 27, 4:34*pm, IF function incompetent wrote: 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" wrote: 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 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]She**et1!$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!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
Is G2 = 0?
"IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
Yes it does now
"Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
G2 should be a value relating to the grade in F2.
"IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
It also said something about a circular reference
"IF function incompetent" wrote: 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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
Formula goes in H2. G2 should be a pay rate.
"IF function incompetent" wrote: 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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
I see, I have my pay rate set up in E2, Pay grade in F2, and the % that I
need the Vlookup for in G2. Let me move things around and I'll get back to you. "Jim" wrote: Formula goes in H2. G2 should be a pay rate. "IF function incompetent" wrote: 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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
Oh I think it worked!! Can you check your formula and punch in the pay amt as
63,502.40 and the pay grade as 18 and tell me if you get 104% ? "Jim" wrote: Formula goes in H2. G2 should be a pay rate. "IF function incompetent" wrote: 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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
next to impossible
YOU ARE THE BEST JIM!!!! Thank you soooo much I couldn't have done it without
you!! "IF function incompetent" wrote: Oh I think it worked!! Can you check your formula and punch in the pay amt as 63,502.40 and the pay grade as 18 and tell me if you get 104% ? "Jim" wrote: Formula goes in H2. G2 should be a pay rate. "IF function incompetent" wrote: 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" wrote: G2 should be a value relating to the grade in F2. "IF function incompetent" wrote: Yes it does now "Jim" wrote: Is G2 = 0? "IF function incompetent" wrote: 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" wrote: 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" wrote: 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" wrote: 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 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]SheÂ*et1!$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!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Impossible | Excel Discussion (Misc queries) | |||
Am I trying the impossible? | Excel Discussion (Misc queries) | |||
impossible way to do something? | Excel Discussion (Misc queries) | |||
IMPOSSIBLE? | Excel Worksheet Functions | |||
Is this impossible? | Excel Worksheet Functions |