"IF" statements in Excel

[jpsouthwick]

Newbie to the forum but not to Excel. I consider myself some what proficient in Excel but I cannot figure out how to create a certain function. I believe I have to use an "IF" statement to accomplish what I am trying to do. I have a fitness spreadsheet that I am trying to create that uses a certain formula for "male" (I23)clients and another formula for "female" (J23) clients. I have my spreadsheet set up so that customers can answer just a couple of questions regarding gender, age, height and weight and based upon the data that is entered a recommended diet and calorie range is given to the individual typing in the information. I have everything working. I just cant figure out how to get the "total" cell(G23) to display one figure for a male and another for a female based upon a client marking an "x" above in a specified male(G7) or female(G8) cell. If any body needs more info just let me know.

 

[Norie]

Why do you have a different cell for male/female?

It would be much easier if it was the same cell.

Then you could use something like this.

=IF(G7="M", I23, J23)

 

[AYankeeCat]

I hope you force people to enter a gender! Formula would be (given the criteria you posted) =IF(G7="X",formula to do calculation for male,formula to do calculation for female) We are assuming if a person is NOT male - the only other choice IS female. (Some days I wonder . . .)

If you have the possibility that they won't enter a gender then the formula need to be nested to look at G7 for an X, if no X then look at G8, if no X there give an error message.

IF function always gives the test then a comma and what to do if the test is true then a comma and what to do if the test result if not true. If you need to do more than one test to get to the answer - then you "nest" a new IF statement inside the commas - either the true or false results segment.

 

[jpsouthwick]

good idea... change made. Now on to the problem with the total. Here are the formulas for male and female clients

Male - =SUM(66+(13.7*(G13/2.2))+(5*(G11*2.54))-(6.8*G9))
Female - =SUM(655+(9.6*(G13/2.2))+(1.8*(G11*2.54))-(4.7*G9))

What I am trying to do is get a total displayed in (G23) based upon the formulas used in (I23) or (J23) which depends on whether the user enters "m" or "f" in cell (G7). Any ideas? Thanks for the previous suggestion.

 

[Norie]

Sorry I'm not following you.

What 'total' are you talking of?

Where are these 2 formula on the worksheet?

By the way why are you using SUM? As far as I can see it's unnecessary.

 

[ngc2392]

hmmm...if i see that correctly...then you probably just make an if statement like this...if x = f then sum for male or sum for female...?
=if(g7="m";sum(blahblah);sum(blahbla) like that?

 

[jpsouthwick]

I appreciate all the help from you guys. I think I found what I was looking for. In Microsoft Excel Help I found out about "Nested" IF Statements and came up with the following function...

=IF(G7="M",I23,IF(G7="F",J23))

Note that the classic "True", "False" of the "logical test" built into the IF statement function doesn't work with what I was wanting to do. I wanted one value (based upon one calculation) for females and one value (based upon another calculation) for males. I didn't want one calculation to be used based upon a "true" statement and if that statement were false to ALWAYS use another calculation. I wanted a little more interaction from my clients and the "nested" IF statements accomplished what I was trying to do.

 

[jpsouthwick]