| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and Hlookup equation
Can someone please explain the following formula to me?
=IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
It's saying, if B4 is Male/Female, and if E4 has more then 0 Push Ups, then
VLOOKUP the push up number in the PUDATA which is probably the data on the Push-Up sheet. So then, if the person is a male, look up the Age of the person, in the Push-Up sheet (The PUAGE name is probably set up using Range (B91:T92) of the Push Up sheet). So this HLOOKUP returns the Column number for the Vlookup of the number of Push ups the person does. So for F4 as the example... it goes in this order... 1) Is the person a Male or Female? TRUE, so... 2) Did the person do more then 0 Push Ups? TRUE, so... 3) VLOOKUP 15 (# of Push ups), using the PUDATA Array (most likely the Push Ups Sheet). So find the #15 in column A... then 4) Is the person Male or Female? Male, so... 5) HLOOKUP the persons Age, go down to the PUAGE Array (Bottom of Push Ups) and 6) Find the persons age and give the value... in this case the value is 18 7) So in the end it looks like, since the person is a male and did more then 0 push ups, look up the number of Push Ups and go over 18 columns, and the score should be 57. If the person were female it would do the exact same thing except take a value that is 1 more then the males value, as in it would use column 19 instead of column 18. I could give more detail but the web site won't let me enter the formula or view the names of the ranges... like Im just guessing what the PUAGE range is. =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 15, 11:45 am, "Don Guillett" wrote:
I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"*") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message oups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - Don, thank you for your response. This formula works great and seems much cleaner. It is definitely easier for me to understand. Thanks again. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 15, 12:10 pm, AKphidelt
wrote: It's saying, if B4 is Male/Female, and if E4 has more then 0 Push Ups, then VLOOKUP the push up number in the PUDATA which is probably the data on the Push-Up sheet. So then, if the person is a male, look up the Age of the person, in the Push-Up sheet (The PUAGE name is probably set up using Range (B91:T92) of the Push Up sheet). So this HLOOKUP returns the Column number for the Vlookup of the number of Push ups the person does. So for F4 as the example... it goes in this order... 1) Is the person a Male or Female? TRUE, so... 2) Did the person do more then 0 Push Ups? TRUE, so... 3) VLOOKUP 15 (# of Push ups), using the PUDATA Array (most likely the Push Ups Sheet). So find the #15 in column A... then 4) Is the person Male or Female? Male, so... 5) HLOOKUP the persons Age, go down to the PUAGE Array (Bottom of Push Ups) and 6) Find the persons age and give the value... in this case the value is 18 7) So in the end it looks like, since the person is a male and did more then 0 push ups, look up the number of Push Ups and go over 18 columns, and the score should be 57. If the person were female it would do the exact same thing except take a value that is 1 more then the males value, as in it would use column 19 instead of column 18. I could give more detail but the web site won't let me enter the formula or view the names of the ranges... like Im just guessing what the PUAGE range is. =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - AKphidelt, thank you for the response. Your post is exactly what I was looking for. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 15, 11:45 am, "Don Guillett" wrote:
I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"*") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message oups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ") |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 15, 2:12 pm, SGT Buckeye wrote:
On May 15, 11:45 am, "Don Guillett" wrote: I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"**") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message roups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ")- Hide quoted text - - Show quoted text - Dan, using your formula above as a guide, I was able to redo the formulas for sit-ups and the 2 mile run. Thanks for the assistance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
Had you just used
17 22 27 32 37 42 47 52 57 62 vs 17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61 62+ then you could have used a simpler formula. Name ranges to suit but do NOT put a - in a tab name. Use _ or a space instead. =IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH( C23,PUSHUP!$A$1:$U$1)+IF(B23="m",0,1))) =IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+ IF(B23="m",0,1))) BTW, what is this for and for who? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message ups.com... On May 15, 2:12 pm, SGT Buckeye wrote: On May 15, 11:45 am, "Don Guillett" wrote: I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"**") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message roups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ")- Hide quoted text - - Show quoted text - Dan, using your formula above as a guide, I was able to redo the formulas for sit-ups and the 2 mile run. Thanks for the assistance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 15, 4:01 pm, "Don Guillett" wrote:
Had you just used 17 22 27 32 37 42 47 52 57 62 vs 17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61 62+ then you could have used a simpler formula. Name ranges to suit but do NOT put a - in a tab name. Use _ or a space instead. =IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH( C23,PUSHUP!$A$1:$U$1)+IF(*B23="m",0,1))) =IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+ IF(B23="m",0,1))) BTW, what is this for and for who? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message ups.com... On May 15, 2:12 pm, SGT Buckeye wrote: On May 15, 11:45 am, "Don Guillett" wrote: I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"***") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message roups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ")- Hide quoted text - - Show quoted text - Dan, using your formula above as a guide, I was able to redo the formulas for sit-ups and the 2 mile run. Thanks for the assistance.- Hide quoted text - - Show quoted text - This is for my Army unit. It will be used to calculate soldiers scores on the Army Physical Fitness Test. Normally, we have to do this manually by looking at a sheet and finding the correct information. As you can imagine, this is quite time consuming when you have an entire unit to score. This spreadsheet will cut down the time it takes to compile scores. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
As an ex USAF officer, I'm glad to help. I was once a Hq Co CO so am familiar. A good effort but it could have been easier using better design. -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message oups.com... On May 15, 4:01 pm, "Don Guillett" wrote: Had you just used 17 22 27 32 37 42 47 52 57 62 vs 17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61 62+ then you could have used a simpler formula. Name ranges to suit but do NOT put a - in a tab name. Use _ or a space instead. =IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH( C23,PUSHUP!$A$1:$U$1)+IF(*B23="m",0,1))) =IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+ IF(B23="m",0,1))) BTW, what is this for and for who? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message ups.com... On May 15, 2:12 pm, SGT Buckeye wrote: On May 15, 11:45 am, "Don Guillett" wrote: I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"***") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message roups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ")- Hide quoted text - - Show quoted text - Dan, using your formula above as a guide, I was able to redo the formulas for sit-ups and the 2 mile run. Thanks for the assistance.- Hide quoted text - - Show quoted text - This is for my Army unit. It will be used to calculate soldiers scores on the Army Physical Fitness Test. Normally, we have to do this manually by looking at a sheet and finding the correct information. As you can imagine, this is quite time consuming when you have an entire unit to score. This spreadsheet will cut down the time it takes to compile scores. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup and Hlookup equation
On May 16, 5:07 pm, "Don Guillett" wrote:
As an ex USAF officer, I'm glad to help. I was once a Hq Co CO so am familiar. A good effort but it could have been easier using better design. -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message oups.com... On May 15, 4:01 pm, "Don Guillett" wrote: Had you just used 17 22 27 32 37 42 47 52 57 62 vs 17-21 22-26 27-31 32-36 37-41 42-46 47-51 52-56 57-61 62+ then you could have used a simpler formula. Name ranges to suit but do NOT put a - in a tab name. Use _ or a space instead. =IF(E23<1,"",VLOOKUP(E23,PUSHUP!$A$1:$AS$88,MATCH( C23,PUSHUP!$A$1:$U$1)+IF(**B23="m",0,1))) =IF(E23<1,"",VLOOKUP(E23,PUdata,MATCH(C23,PUrow1)+ IF(B23="m",0,1))) BTW, what is this for and for who? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message oups.com... On May 15, 2:12 pm, SGT Buckeye wrote: On May 15, 11:45 am, "Don Guillett" wrote: I think I would have written the formula a bit differently hlookup(c4,puage,2)+if(b4="m",1,0) but it appears to be doing an hlookup to find which column to return from the pudata range. =IF(OR(B4"A",E40),VLOOKUP(E4,PUDATA,HLOOKUP(C4,P UAGE,2)+IF(B4="m",0,1)),"****") Perhaps b4"A" should be D4"A" or ??? -- Don Guillett SalesAid Software "SGT Buckeye" wrote in message roups.com... Can someone please explain the following formula to me? =IF(B4"A",(IF(E40,VLOOKUP(E4,PUDATA,(IF(B4="M",( HLOOKUP(C4,PUAGE,2)), (HLOOKUP(C4,PUAGE,2)+1)))),0))," ") It is taken from cell F4 on the "Input Data" worksheet located at this URL: http://www.armystudyguide.com/conten...rd-in-mass.xls I know how to write Vlookups and this worksheet works fine. I am just curious to know how the combination of Vlookup and Hlookup works in case I need to modify some of the scoring parameters in the future. Thank you in advance for your help.- Hide quoted text - - Show quoted text - I was wondering if you could posibly simplify this formula as well? =IF(B4"A",IF(I40,IF(VLOOKUP(I4,DATA2MI, 1)<I4,VLOOKUP(I4+0.00007,DATA2MI,(IF(B4="M",(HLOOK UP(C4,MIAGE,2)), (HLOOKUP(C4,MIAGE,2)+1)))),VLOOKUP(I4,DATA2MI,(IF( B4="M", (HLOOKUP(C4,MIAGE,2)),(HLOOKUP(C4,MIAGE,2)+1))))), 0)," ")- Hide quoted text - - Show quoted text - Dan, using your formula above as a guide, I was able to redo the formulas for sit-ups and the 2 mile run. Thanks for the assistance.- Hide quoted text - - Show quoted text - This is for my Army unit. It will be used to calculate soldiers scores on the Army Physical Fitness Test. Normally, we have to do this manually by looking at a sheet and finding the correct information. As you can imagine, this is quite time consuming when you have an entire unit to score. This spreadsheet will cut down the time it takes to compile scores.- Hide quoted text - - Show quoted text - I modified the worksheet that contained the run time and points earned data to include headers in the pattern M17, F17, M18, F18. I then sorted the information in descending order by run time (see table below). Time M17 F17 M18 F18 26:36 0 0 0 0 26:30 0 0 0 0 26:24 0 0 0 0 26:18 0 0 0 0 I then used the following formula using a double lookup: =IF(ISBLANK($J4)," ",IF($J4=RUN!A2,0,IF($J4<=RUN! A138,100,OFFSET(RUN! $A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0))))) Thanks for all the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP , HLOOKUP | Excel Discussion (Misc queries) | |||
| vlookup & hlookup | Excel Worksheet Functions | |||
| vlookup and hlookup | Excel Worksheet Functions | |||
| what are the uses for Vlookup and Hlookup? | Excel Worksheet Functions | |||
| Vlookup and Hlookup | Excel Worksheet Functions | |||
Linear Mode
