Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Full Handicap Playing Handicap
4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Bob
What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
I am using the same as you, would it be the size of the range, as it goes
o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Bob
But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Roger
I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Bob
If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Roger
Thank you for you kind offer but i got so angry i deleted the sheet to move in a different direction. I am writing a golf score card( based on just my home club) but would like to expand to other clubs. The yardage and stroke index will be different to my club, i have set formulas for each hole based on the players handicap, to calculate both net score and stableford points, just by inputing the gross score. =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) this is for a par 4 stroke index 10. what i would like to do is have a list of index 1 - 18 with the appropiate code next to it so the user can select which index is on a certain hole and the correct code will slot into the correct net. can this be done using lookup? i hope this makes sense Regards Bob "Roger Govier" wrote: Hi Bob If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Bob
Firstly, I am a non-golfer, so other than understanding what par, birdie and eagle are from watching occasional golf on the TV, I haven't a clue. From your description, what is being entered in E18? what values do D3 and D18 hold, and are they fixed or do they alter relative to E18? we have a constant of 28, what is that for? what table of values do you want to lookup? I am sure Vlookup, or Index and Match will provide a solution for you, but I need a better understanding of exactly what you are trying to achieve. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger Thank you for you kind offer but i got so angry i deleted the sheet to move in a different direction. I am writing a golf score card( based on just my home club) but would like to expand to other clubs. The yardage and stroke index will be different to my club, i have set formulas for each hole based on the players handicap, to calculate both net score and stableford points, just by inputing the gross score. =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) this is for a par 4 stroke index 10. what i would like to do is have a list of index 1 - 18 with the appropiate code next to it so the user can select which index is on a certain hole and the correct code will slot into the correct net. can this be done using lookup? i hope this makes sense Regards Bob "Roger Govier" wrote: Hi Bob If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Roger
=IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) E18= gross score D3 players handicap D18= stroke index(lower the harder the hole plays) This code is for a stroke index of 10, which reads if gross score is blank,leave blank, if handicap is greater-equal and less than 28 then say if gross score is 5 strokes it will return 4 if the handicap is less than the stroke index then the gross score entered will remain the same lastly if the handicap is 28 then gross score of 5 will return 3( this is if a player has a handicap of 28 then he has an extra 1 shot on each hole plus an additional shot for stroke index 1-10) handicap of 19 will get 18 shots for card plus additional shot on SI 1 On my home card each stroke index has its own code which determines variations to each handicap. For my home course the above code is in relation to hole#6, but on another course SI 10 could be on hole 8 or 17, so how would i apply each SI code to be applied no matter what course details i input I have tried to copy paste formula to another sheet and used lookup(SI,range) but this did not work, is there a macro which would run this? I am sorry to be a pain, and would like to thank you thus far Regards Bob "Roger Govier" wrote: Hi Bob Firstly, I am a non-golfer, so other than understanding what par, birdie and eagle are from watching occasional golf on the TV, I haven't a clue. From your description, what is being entered in E18? what values do D3 and D18 hold, and are they fixed or do they alter relative to E18? we have a constant of 28, what is that for? what table of values do you want to lookup? I am sure Vlookup, or Index and Match will provide a solution for you, but I need a better understanding of exactly what you are trying to achieve. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger Thank you for you kind offer but i got so angry i deleted the sheet to move in a different direction. I am writing a golf score card( based on just my home club) but would like to expand to other clubs. The yardage and stroke index will be different to my club, i have set formulas for each hole based on the players handicap, to calculate both net score and stableford points, just by inputing the gross score. =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) this is for a par 4 stroke index 10. what i would like to do is have a list of index 1 - 18 with the appropiate code next to it so the user can select which index is on a certain hole and the correct code will slot into the correct net. can this be done using lookup? i hope this makes sense Regards Bob "Roger Govier" wrote: Hi Bob If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Hi Bob
Probably easiest if you mail me a copy of the Sheet, with the extra sheet which has the table of values for different holes, on different course and I will see if I can resole it for you. Remove NOSPAM from address to mail direct. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) E18= gross score D3 players handicap D18= stroke index(lower the harder the hole plays) This code is for a stroke index of 10, which reads if gross score is blank,leave blank, if handicap is greater-equal and less than 28 then say if gross score is 5 strokes it will return 4 if the handicap is less than the stroke index then the gross score entered will remain the same lastly if the handicap is 28 then gross score of 5 will return 3( this is if a player has a handicap of 28 then he has an extra 1 shot on each hole plus an additional shot for stroke index 1-10) handicap of 19 will get 18 shots for card plus additional shot on SI 1 On my home card each stroke index has its own code which determines variations to each handicap. For my home course the above code is in relation to hole#6, but on another course SI 10 could be on hole 8 or 17, so how would i apply each SI code to be applied no matter what course details i input I have tried to copy paste formula to another sheet and used lookup(SI,range) but this did not work, is there a macro which would run this? I am sorry to be a pain, and would like to thank you thus far Regards Bob "Roger Govier" wrote: Hi Bob Firstly, I am a non-golfer, so other than understanding what par, birdie and eagle are from watching occasional golf on the TV, I haven't a clue. From your description, what is being entered in E18? what values do D3 and D18 hold, and are they fixed or do they alter relative to E18? we have a constant of 28, what is that for? what table of values do you want to lookup? I am sure Vlookup, or Index and Match will provide a solution for you, but I need a better understanding of exactly what you are trying to achieve. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger Thank you for you kind offer but i got so angry i deleted the sheet to move in a different direction. I am writing a golf score card( based on just my home club) but would like to expand to other clubs. The yardage and stroke index will be different to my club, i have set formulas for each hole based on the players handicap, to calculate both net score and stableford points, just by inputing the gross score. =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) this is for a par 4 stroke index 10. what i would like to do is have a list of index 1 - 18 with the appropiate code next to it so the user can select which index is on a certain hole and the correct code will slot into the correct net. can this be done using lookup? i hope this makes sense Regards Bob "Roger Govier" wrote: Hi Bob If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with lookup
Thanks Roger
That is working now Regards Bob "Roger Govier" wrote: Hi Bob Probably easiest if you mail me a copy of the Sheet, with the extra sheet which has the table of values for different holes, on different course and I will see if I can resole it for you. Remove NOSPAM from address to mail direct. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) E18= gross score D3 players handicap D18= stroke index(lower the harder the hole plays) This code is for a stroke index of 10, which reads if gross score is blank,leave blank, if handicap is greater-equal and less than 28 then say if gross score is 5 strokes it will return 4 if the handicap is less than the stroke index then the gross score entered will remain the same lastly if the handicap is 28 then gross score of 5 will return 3( this is if a player has a handicap of 28 then he has an extra 1 shot on each hole plus an additional shot for stroke index 1-10) handicap of 19 will get 18 shots for card plus additional shot on SI 1 On my home card each stroke index has its own code which determines variations to each handicap. For my home course the above code is in relation to hole#6, but on another course SI 10 could be on hole 8 or 17, so how would i apply each SI code to be applied no matter what course details i input I have tried to copy paste formula to another sheet and used lookup(SI,range) but this did not work, is there a macro which would run this? I am sorry to be a pain, and would like to thank you thus far Regards Bob "Roger Govier" wrote: Hi Bob Firstly, I am a non-golfer, so other than understanding what par, birdie and eagle are from watching occasional golf on the TV, I haven't a clue. From your description, what is being entered in E18? what values do D3 and D18 hold, and are they fixed or do they alter relative to E18? we have a constant of 28, what is that for? what table of values do you want to lookup? I am sure Vlookup, or Index and Match will provide a solution for you, but I need a better understanding of exactly what you are trying to achieve. -- Regards Roger Govier "Bob" wrote in message ... Hi Roger Thank you for you kind offer but i got so angry i deleted the sheet to move in a different direction. I am writing a golf score card( based on just my home club) but would like to expand to other clubs. The yardage and stroke index will be different to my club, i have set formulas for each hole based on the players handicap, to calculate both net score and stableford points, just by inputing the gross score. =IF(E18="","",IF((AND(D3=D18,D3<28)),E18-1,IF(D3<D18,E18,IF(D3=28,E18-2)))) this is for a par 4 stroke index 10. what i would like to do is have a list of index 1 - 18 with the appropiate code next to it so the user can select which index is on a certain hole and the correct code will slot into the correct net. can this be done using lookup? i hope this makes sense Regards Bob "Roger Govier" wrote: Hi Bob If you want to mail me a copy of the sheet direct, I'll take a look Remove NOSPAM from my email address to send -- Regards Roger Govier "Bob" wrote in message ... Hi Roger I am using type data =IF(A3="","",LOOKUP(A3,K3:L283)) this is a copy/paste above, i have added another decimal, 4.0 now shows 4.00 i did do another lookup just using 0.1 through to 1.8 with increases of 0.1, which worked but anything larger still returns for eg 4.5 as 4 instead of 5 This is driving me mad Regards Bob "Roger Govier" wrote: Hi Bob But you say it is returning an answer of 4 for 4.4 and 5 for 4.6. Those don't look like the returns from Lookup, but more like the returns from =ROUND(A1,0). Also, are the values in your column of data, type values or values returned from formulae? What happens if you change the format of the cells to a greater number of decimals? Does 4.5 show up as 4.499 or something similar? -- Regards Roger Govier "Bob" wrote in message ... I am using the same as you, would it be the size of the range, as it goes o.1,0.2,all the way to 28? Every other sheet with lookup works fine but not this one bob "Roger Govier" wrote: Hi Bob What formula are you using? =LOOKUP(4.5,A2:A5) returns 4.5 for me -- Regards Roger Govier "Bob" wrote in message ... Full Handicap Playing Handicap 4.3 4 4.4 4 4.5 5 4.6 5 Lookup will return 4 if i am looking for 4.4, and 5 if i look for 4.6. But for some reason on all the #.5 will always return with the wrong result. For eg 4.5 will return 4 not 5. Any ideas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |