-1

Am I able to combine these 3 formulas into one formula?

=IF(ISBLANK(R32),0)
=IF(R32<12,1)
=IF(R32>11,1.5)

I want to return counting the cells worth to zero if no entry is made, but if the cell is over 11 then that cells value is 1.5 and if lower the value is 1 unless blank then blank.

1
  • 2
    The second and third conditions are both true for values between 11 and 12. Which should take precedence?
    – AFH
    Sep 16, 2016 at 0:12

2 Answers 2

4

To strictly combine your formulae you would have =IF(ISBLANK(R32),0,IF(R32<12,1,IF(R32>11,1.5,""))), however you can streamline it as you will not get to the "" for a FALSE result in the last nested IF statement because the last IF statement cannot be false.

The strict combination does what you want but you could use =IF(ISBLANK(R32),0,IF(R32<12,1,1.5)).

What the streamlined formula does is first check to see if R32 is blank. If it is then insert a 0. If it isn't blank then it goes to the next IF statement. If R32 is less than 12 then insert a 1. If it isn't then it has got to be either 12 or higher so insert 1.5.

I am a little confused though with your last requirement which was "if blank then blank". You already have the first IF statement inserting a 0 if blank. You need to choose whether you want a 0 or blank if R32 is blank (no entry made). If you want a blank, change the 0 in the first IF statement to a ""

Another point of note is that to check for a blank I personally find it is good to use OR(R32="",R32=" ") instead of ISBLANK(R32), especially if you are sharing the sheet with someone else.

The reason being that I have lost count of the number of times someone has put a space into a cell to make it blank but it isn't. Excel will see the space as data as you have a character in the cell. This can cause confusion and lost time looking for the problem when you don't get the results you expect.

0

If has a third argument, it is what to when false. You would nest the IFs inside each other using the FALSE argument:

=IF(R32="",0,IF(R32<12,1,1.5))

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.