2

I'm working on this spreadsheet, field B10.

https://docs.google.com/spreadsheets/d/1wC-y0T9QeF6MZALavljzqVLJTfadSHmGt0yOFYgIMnE/edit?usp=sharing

I'd like to apply this formula, but I'm not sure how:

IF B10=TRUE AND B5=TRUE, THEN C10xG6
BUT 
IF B10=TRUE AND B5=FALSE, THEN C10*F6.
IF B10=FALSE, " ". 

Any help would be appreciated!

4 Answers 4

1

I believe this formula is more understandable and adherent to the request for a beginner:

=ifs(
and(B10=TRUE , B5=TRUE) , C10*G6,
and(B10=TRUE ,B5=FALSE) , C10*F6,
B10=FALSE, ""
)
1
2

Indeed, as already shown, two nested IF() functions solve your problem (See the Nested_Outcome formula in the Examples section)

I want to show another way of calculating for combined conditions, without the IF() function.

Let's present all combinations of your conditions in the form of a table:

First Table

(I wrote "Something else" to show that for the combination of B10=FALSE AND B5=TRUE some other option is possible, and not just a space, as in your case)

Considering that FALSE is equal to 0 and TRUE is equal to 1 (at least for Google Spreadsheets this is the case), let's rewrite this table in a general form:

Second Table

You can get the option number from the B5 and B10 values with a very simple formula: =B10*2+B5+1

Now use the CHOOSE() function: specify this expression as the first parameter index, and then list all four possible options separated by commas -

=CHOOSE(B10*2+B5+1,"","",C10*F6,C10*G6)

Of course, for two conditions, the benefit from this approach is not very noticeable. But the same principle can be used for both three and four conditions - it is enough to multiply the value of each condition by the next power of 2:

=CHOOSE(4th_condition*8+3rd_condition*4+2nd_condition*2+1st_condition+1,...)

Yes, the formula will be quite long. But in any case, it will be shorter and clearer than eight nested IF() functions.

1
  • 1
    @ JohnSUN change your formula in =CHOOSE(B10*2+B5+1,"","",C10*F6,C10*G6)
    – Daniele
    Oct 27 at 7:42
1

=IF(B10=TRUE,IF(B5=TRUE,C10*G6,C10*F6),"")

Hope it helps!

2
  • You can remove the two ‘=TRUE’ as they are redundant.
    – CodeCamper
    Oct 27 at 4:21
  • Hello! I know, I preferred it to remain more clear and direct from the original logic suggested ;)
    – Martín
    Oct 27 at 6:43
1

Your pseudo code example:

IF B10=TRUE AND B5=TRUE, THEN C10xG6 
BUT 
IF B10=TRUE AND B5=FALSE, THEN C10*F6.
IF B10=FALSE, " ". 

How to accomplish in google sheets:

=if(n(B10)=n(whatthefoxsay())," ",if(B5,multiply(C10,G6),multiply(C10,F6)))
2
  • small mistake ... change this part = if (n (B11) with this = if (n (B10)
    – Daniele
    Oct 27 at 7:49
  • @Daniele changed it to B10
    – CodeCamper
    Oct 27 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.