0

Unlike a basic IF statement, I need Excel to calculate one formula if a designated cell is blank but a slightly different formula if the designated cell has a value higher than 0.

If an existing employee has monthly base pay, commission, overtime, and shift differential which need to be added to obtain gross pay – no problem, simple addition.

However, I need to add a column next to monthly base pay for any new employees' monthly pro-rated pay amount. I need Excel to look to see if there is a value in the pro-rated pay column first. If yes, I need it to add pro-rated pay + commission + overtime + shift differential to compute gross pay. If the pro-rated pay column is blank, I need it to add full monthly pay + commission + overtime + shift differential to calculate gross pay.

To clarify: if the pro-rated pay is not blank, I want the sum to exclude the full monthly pay.

  • Just use SUM() it will ignore the blank cell when blank and add it if there is a value. So SUM(B2:E2) – Scott Craner Jan 12 '17 at 16:33
  • Why can't you just sum all the columns? If the cell is empty, Excel will just ignore it anyway. – Jonny Wright Jan 12 '17 at 16:33
  • (1) Please try to be consistent.  I can guess that “Monthly Pay” ≡ “monthly base pay”, but your picture shows “Pro-rate for first month” and “Bonus”, and your text mentions “commission”, “shift differential” and “monthly pro-rated pay amount”.  It is especially confusing that you say, “I need [to add] a column next to monthly base pay for any new employees monthly pro-rated pay amount.” when, in fact, it’s already there.  … (Cont’d) – G-Man Jan 13 '17 at 4:32
  • (Cont’d) …  (2) Please don’t post pictures of text (like screenshots). Just type in a textual representation of your data, as was done here and here. (3) It helps to show desired results. If your example showed Gross Pay for Barney Fife to be $4026.57 (3653.12 + 250.00 + 123.45), people wouldn’t have had to work so hard to understand your question. – G-Man Jan 13 '17 at 4:34
0

If I've understood correctly (your question mentions shift differential but the example image doesn't, also I'm assuming "commission" in the question equates to the bonus column in the example), you want something like this in the gross pay column:

=IF(C2>0,(C2+D2+E2),(B2+D2+E2))

If the value of C2 (the pro-rate pay column) is more than 0, then add C2, D2 and E2 (the pro-rata, bonus and overtime). If not, then add B2,D2 and E2 (monthly pay, bonus and overtime).

  • Replying to the comments on the OP (not enough rep to do it properly), in one instance you don't want the monthly pay added so simply summing the columns won't work. – Darren Jan 12 '17 at 16:37
  • You are right I did not read all the way, Here is a slightly shorter version of yours: =IF(C2<>"",C2,B2)+D2+E2. Also if the value in C2 is an empty string and not an actual empty cell it will be > 0, so testing OP may want to test for the null string instead. – Scott Craner Jan 12 '17 at 16:47
  • I was originally going to use =isblank or =isnumber to verify the content of the cell before trying to add it, but I believe my first response (or your tweak) should suffice. – Darren Jan 12 '17 at 16:50
0

This looks like a relatively simple IF statement to me:

If(C2>0, sum(c2:e2), sum(B2:E2)))

  • I do not get it – Pierre.Vriens May 17 '17 at 16:55
  • Brian, could you perhaps explain your answer more thoroughly? How does this answer OP? – music2myear May 17 '17 at 18:49

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.