| Home |
| Search |
| Today's Posts |
|
|
|
#2
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. |
|
#3
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)," ") |
|
#4
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. |
|
#5
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. |
|
#6
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. |
|
#7
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. |
|
#8
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 | |||
Hybrid Mode
