Ask Your Question
1

Probably to many IF statements

asked 2019-07-02 22:21:30 +0100

bander1 gravatar image

updated 2019-07-03 00:10:23 +0100

=IF(C11="Fairway",IF(C12="3i (225)",IF(C13="Full",IF(C14="100%",C8/B45))))

Trying to get the reset of C8/B45 if the other cells have the correct information of C11=Fairway, C12=3i (225),C13=Full and C14=100%

But just get a 'FALSE' message appearing, its probably to many IF statements in the equation but i'm reaching the limit of my spreadsheet knowledge to fix it, hope you can help.

Cells C11 to C14 are text from a selection list, e.g. C11 has the option of Fairway or Tee. Any changes would result in cell C8 being divided by a different number.

I've attached the file here:

C:\fakepath\Shot Calc.ods

edit retag flag offensive close merge delete

Comments

Trying to get the reset of C8/B45

I do not understand that. What is in the cells?

Try to test the IF statements individually. Are you sure that all cells C11 to C14 are formatted as text?

Better still, if you upload the file here. Edit your initial question and click on the "Attachment" icon.

ebot gravatar imageebot ( 2019-07-02 23:00:17 +0100 )edit

Like @ebot, I don't understand what you are trying to do. You seem to have a hierarchy of IF tests (which I don't understand). On the assumption that you had in mind instead a simple series of IF tests, it might help you to know that tests can be combined into one IF test by using the AND logical function. (Not sure if it works with OR.)
The general form is =IF(test,then,else)

which can be expanded to =IF((test1)AND(test2)AND(test3)AND(test4),then,else)

Hope this helps.

ve3oat gravatar imageve3oat ( 2019-07-03 00:44:34 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2019-07-03 02:19:46 +0100

Hi, @bander1, see pictures

image description

In C15, set the formula:

=IF(AND(C11="Fairway";C12="3i (225)";C13="Full";C14="100%");C8/B45;"xxx")

xxx = when negative

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

Thank you very much everyone for responding with the answer and all the comments.

bander1 gravatar imagebander1 ( 2019-07-03 22:41:28 +0100 )edit
0

answered 2019-07-03 02:15:34 +0100

The problem is that in C14, you have not a text "100%", but a number 1 (=100%). So, simply modifying your formula to

=IF(C11="Fairway";IF(C12="3i (225)";IF(C13="Full";IF(C14=100%;C8/B45))))

will fix the issue (note that I just removed the double quotes around 100%).

Or do as suggested by @ve3oat:

=IF(AND(C11="Fairway";C12="3i (225)";C13="Full";C14=100%);C8/B45)
edit flag offensive delete link more

Comments

Thank you @Mike Kaganski. I didn't know that the several tests could be collected into one AND() expression. Very neat that way.

ve3oat gravatar imageve3oat ( 2019-07-03 03:14:16 +0100 )edit

Actually the test AND test AND test AND ... syntax is compatibility-only, and is deprecated - I had been told by @erAck about that when suggested that syntax to someone.

Mike Kaganski gravatar imageMike Kaganski ( 2019-07-03 04:31:59 +0100 )edit

Nice! And thanks for passing it on.

ve3oat gravatar imageve3oat ( 2019-07-03 19:35:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-02 22:21:30 +0100

Seen: 41 times

Last updated: Jul 03