Google-sheets – Nesting IF AND funtions in Google Sheets

google sheets

I have 3 drop down boxes that I want one cell to choose an answer for based on the selections for each drop down. I can use the IF AND function to produce the one scenario of the result I want but I can't get it to do it for more than one. Here is that formula:

=if((AND(A13="Picasso 1000",C13="RIC",E13="Batteries")),"$4,392","")

I either can't figure out how to properly nest several of these types of formulas for other outcomes or I am not using the right kind of formula to produce the result I need.

Best Answer

You don't need any IF()s.

You can setup the formula as a set of mutually exclusive Boolean factors times payoffs like:

=factor1*134.54 + factor2*56.98 + factor3*100.34 + ...

just insure that only one of the factors is 1 and all the others are 0. For example:

=AND(a1="green",a2="green",a3="green")*134.54 + AND(a1="red",a2="red",a3="red")*56.98 + AND(a1=5,a2=6,a3=99)*100.34

only one of the three terms can contribute.

Related Solutions

Google-sheets – Daily running average in Google Sheets possible or not

If you have those tips down column A, rows 1-5, you could put the following in cell B1, or wherever you choose to have your running daily average field.

=SUM($A$1:A1)/ROWS($A$1:A1)

Then you will want to fill that formula (dragging the little + at the lower right corner of the B1 cell) down through the rest of your days. When you fill the formula, the anchored cell will remain for every formula, but each new row/day will add a new value and count into the calculation.

The resulting formulas should be:

=SUM($A$1:A1)/ROWS($A$1:A1)
=SUM($A$1:A2)/ROWS($A$1:A2)
=SUM($A$1:A3)/ROWS($A$1:A3)
=SUM($A$1:A4)/ROWS($A$1:A4)
=SUM($A$1:A5)/ROWS($A$1:A5)
...

and the resulting values for your daily tips:

34
67
51.6666666666667
46.25
41.8

Google-sheets – Turning data populated by formula into ‘solid’ data

Yes. Either Copy (Ctrl+C) and then, having selected your destination, Edit > Paste special > Paste values only, or when pasting Ctrl+Shift+V.

Best Answer

Related Solutions

Google-sheets – Daily running average in Google Sheets possible or not

Google-sheets – Turning data populated by formula into ‘solid’ data

Related Topic