[LoveExcelButFrustrated]

This is a big one (and a few may follow if I can't figure out the others
based on responses here). I am trying to create a score keeping formula. It
is gender specific for points as well. Here is what I am trying to make the
formula say: if a male gets 2 or less pull ups, its fail and for every one 3
and over is *5 for points. (20 is max for a score of 100.) Now for females,
they do a flex arm hang for 4 seconds or less and fail it or 5 or more
seconds to pass. 5 to 40 seconds are equivalent to on point each but then
after 40 they go by 2's so that 41=42, 42=44, 43=46, etc.. until 70 seconds
which equals 100 points. The basics of the first formula i.e.
=IF(S8<=2,"Fail",S8*5) I got to work, but then can't figure out how to make
it gender specific. My cell M8 is where the M or F goes for gender but when I
try any variation of =IF(M8="M";(S8<=2,"Fail",S8*5) it tells me that that is
invalid, or cannot find library such and such... I have been at this all day
and can't get it. Can anyone help? Don't forget I also need it to say if
M8="F" then blah blah blah in the same formula for those scores. I know it
can be done because I am mirroring a form I already have that uses these
formulas, but guess what? It is protected! HA HA. And the person who made it
left this unit years ago and no one knows who it was or what the password
might be so that I can edit the OTHER fields that aren't acceptible. So if
anyone can tell me how to unprotect this worksheet that would be waaaayyy
easier. Enough for now, can anyone give me a hand?

[Don Guillett]

Look in the help index for
AND

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LoveExcelButFrustrated" m
wrote in message ...
This is a big one (and a few may follow if I can't figure out the others
based on responses here). I am trying to create a score keeping formula.
It
is gender specific for points as well. Here is what I am trying to make
the
formula say: if a male gets 2 or less pull ups, its fail and for every one
3
and over is *5 for points. (20 is max for a score of 100.) Now for
females,
they do a flex arm hang for 4 seconds or less and fail it or 5 or more
seconds to pass. 5 to 40 seconds are equivalent to on point each but then
after 40 they go by 2's so that 41=42, 42=44, 43=46, etc.. until 70
seconds
which equals 100 points. The basics of the first formula i.e.
=IF(S8<=2,"Fail",S8*5) I got to work, but then can't figure out how to
make
it gender specific. My cell M8 is where the M or F goes for gender but
when I
try any variation of =IF(M8="M";(S8<=2,"Fail",S8*5) it tells me that that
is
invalid, or cannot find library such and such... I have been at this all
day
and can't get it. Can anyone help? Don't forget I also need it to say if
M8="F" then blah blah blah in the same formula for those scores. I know it
can be done because I am mirroring a form I already have that uses these
formulas, but guess what? It is protected! HA HA. And the person who made
it
left this unit years ago and no one knows who it was or what the password
might be so that I can edit the OTHER fields that aren't acceptible. So if
anyone can tell me how to unprotect this worksheet that would be waaaayyy
easier. Enough for now, can anyone give me a hand?

[Brad]

You could do this in one cell, but if you take two
Cell one

B2 = your M8
B3 = your S8

Pretend this is cell c2
=(--(B2="m"))*IF(B3<=2,0,MIN(100,B3*5))+(--(B2="f"))*IF(B3<=4,0,IF(B3<40,B3,40+MIN(100,(IF(IS ODD(B3),B3+1,B3)-40)*2)))


cell two
if(c2=0,"fail",c2)

Does this make sense?
--
Wag more, bark less


"LoveExcelButFrustrated" wrote:

This is a big one (and a few may follow if I can't figure out the others
based on responses here). I am trying to create a score keeping formula. It
is gender specific for points as well. Here is what I am trying to make the
formula say: if a male gets 2 or less pull ups, its fail and for every one 3
and over is *5 for points. (20 is max for a score of 100.) Now for females,
they do a flex arm hang for 4 seconds or less and fail it or 5 or more
seconds to pass. 5 to 40 seconds are equivalent to on point each but then
after 40 they go by 2's so that 41=42, 42=44, 43=46, etc.. until 70 seconds
which equals 100 points. The basics of the first formula i.e.
=IF(S8<=2,"Fail",S8*5) I got to work, but then can't figure out how to make
it gender specific. My cell M8 is where the M or F goes for gender but when I
try any variation of =IF(M8="M";(S8<=2,"Fail",S8*5) it tells me that that is
invalid, or cannot find library such and such... I have been at this all day
and can't get it. Can anyone help? Don't forget I also need it to say if
M8="F" then blah blah blah in the same formula for those scores. I know it
can be done because I am mirroring a form I already have that uses these
formulas, but guess what? It is protected! HA HA. And the person who made it
left this unit years ago and no one knows who it was or what the password
might be so that I can edit the OTHER fields that aren't acceptible. So if
anyone can tell me how to unprotect this worksheet that would be waaaayyy
easier. Enough for now, can anyone give me a hand?

[LoveExcelButFrustrated]

Thanks so much Brad, that worked almost perfectly. Here is what ended up
doing the job:
=(--(M8="m"))*IF(S8<=2,0,MIN(100,S8*5))+(--(M8="f"))*IF(S8<=4,0,IF(S8<41,S8,MIN(100,S8*2-40)))

I have another one I am about to post also. I HAVE GOT to learn how to do
all this great stuff!


"Brad" wrote:

You could do this in one cell, but if you take two
Cell one

B2 = your M8
B3 = your S8

Pretend this is cell c2
=(--(B2="m"))*IF(B3<=2,0,MIN(100,B3*5))+(--(B2="f"))*IF(B3<=4,0,IF(B3<40,B3,40+MIN(100,(IF(IS ODD(B3),B3+1,B3)-40)*2)))


cell two
if(c2=0,"fail",c2)

Does this make sense?
--
Wag more, bark less


"LoveExcelButFrustrated" wrote:

This is a big one (and a few may follow if I can't figure out the others
based on responses here). I am trying to create a score keeping formula. It
is gender specific for points as well. Here is what I am trying to make the
formula say: if a male gets 2 or less pull ups, its fail and for every one 3
and over is *5 for points. (20 is max for a score of 100.) Now for females,
they do a flex arm hang for 4 seconds or less and fail it or 5 or more
seconds to pass. 5 to 40 seconds are equivalent to on point each but then
after 40 they go by 2's so that 41=42, 42=44, 43=46, etc.. until 70 seconds
which equals 100 points. The basics of the first formula i.e.
=IF(S8<=2,"Fail",S8*5) I got to work, but then can't figure out how to make
it gender specific. My cell M8 is where the M or F goes for gender but when I
try any variation of =IF(M8="M";(S8<=2,"Fail",S8*5) it tells me that that is
invalid, or cannot find library such and such... I have been at this all day
and can't get it. Can anyone help? Don't forget I also need it to say if
M8="F" then blah blah blah in the same formula for those scores. I know it
can be done because I am mirroring a form I already have that uses these
formulas, but guess what? It is protected! HA HA. And the person who made it
left this unit years ago and no one knows who it was or what the password
might be so that I can edit the OTHER fields that aren't acceptible. So if
anyone can tell me how to unprotect this worksheet that would be waaaayyy
easier. Enough for now, can anyone give me a hand?

[Brad]

Glad to help - I missread the requirements for femals - glad you got it to
work.
--
Wag more, bark less


"LoveExcelButFrustrated" wrote:

Thanks so much Brad, that worked almost perfectly. Here is what ended up
doing the job:
=(--(M8="m"))*IF(S8<=2,0,MIN(100,S8*5))+(--(M8="f"))*IF(S8<=4,0,IF(S8<41,S8,MIN(100,S8*2-40)))

I have another one I am about to post also. I HAVE GOT to learn how to do
all this great stuff!


"Brad" wrote:

You could do this in one cell, but if you take two
Cell one

B2 = your M8
B3 = your S8

Pretend this is cell c2
=(--(B2="m"))*IF(B3<=2,0,MIN(100,B3*5))+(--(B2="f"))*IF(B3<=4,0,IF(B3<40,B3,40+MIN(100,(IF(IS ODD(B3),B3+1,B3)-40)*2)))


cell two
if(c2=0,"fail",c2)

Does this make sense?
--
Wag more, bark less


"LoveExcelButFrustrated" wrote:

This is a big one (and a few may follow if I can't figure out the others
based on responses here). I am trying to create a score keeping formula. It
is gender specific for points as well. Here is what I am trying to make the
formula say: if a male gets 2 or less pull ups, its fail and for every one 3
and over is *5 for points. (20 is max for a score of 100.) Now for females,
they do a flex arm hang for 4 seconds or less and fail it or 5 or more
seconds to pass. 5 to 40 seconds are equivalent to on point each but then
after 40 they go by 2's so that 41=42, 42=44, 43=46, etc.. until 70 seconds
which equals 100 points. The basics of the first formula i.e.
=IF(S8<=2,"Fail",S8*5) I got to work, but then can't figure out how to make
it gender specific. My cell M8 is where the M or F goes for gender but when I
try any variation of =IF(M8="M";(S8<=2,"Fail",S8*5) it tells me that that is
invalid, or cannot find library such and such... I have been at this all day
and can't get it. Can anyone help? Don't forget I also need it to say if
M8="F" then blah blah blah in the same formula for those scores. I know it
can be done because I am mirroring a form I already have that uses these
formulas, but guess what? It is protected! HA HA. And the person who made it
left this unit years ago and no one knows who it was or what the password
might be so that I can edit the OTHER fields that aren't acceptible. So if
anyone can tell me how to unprotect this worksheet that would be waaaayyy
easier. Enough for now, can anyone give me a hand?