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.