1

I have 14 different IF formulas I am trying to combine. They are all different calculations (see below). For now I have each formula in a different column and then a new column which adds the values on each row to get one column with all the numbers I need (each row will only have one value). Is there a better way to do this?

L Column =IF(K2="Regular",H2,IF(AND(K2="Regular Attendance Penalty",E2="TEMPRILEY"),(H2/1.38)-1)*1.38)

M Column =IF(AND(K2="Regular Attendance Penalty",E2="TEMPTRC"),(H2/1.4)-1)*1.4

N Column =IF(AND(K2="Overtime",E2="TEMPTRC"),(H2/1.5)*1.25)

O Column =IF(AND(K2="OT Regular Attendance Penalty OT",E2="TEMPTRC"),(H2/1.4)-1)*1.4*1.25

P Column =IF(AND(K2="Regular Attendance Penalty",E2="TEMPINT"),(H2/1.4)-1)*1.4

Q Column =IF(AND(K2="Overtime",E2="TEMPINT"),(H2/1.5)/1.4)*1.5*1.34)

R Column =IF(AND(K2="OT Regular Attendance Penalty OT",E2="TEMPRILEY"),(H2/1.38)-1)*1.5*1.38

S Column =IF(AND(K2="Overtime",E2="TEMPRILEY"),H2)

T Column =IF(AND(K2="OT Regular Attendance Penalty OT",E2="TEMPINT"),(H2/1.4)-1)*1.5*1.34

U Column =IF(AND(K2="Regular 2nd Shift Attendance Penalty",E2="TEMPINT2nd"),(H2/2/1.4)-0.75)*1.4

V Column =IF(AND(K2="Regular 2nd Shift Attendance Penalty",E2="TEMPTRC2nd"),(H2/2/1.4)-0.75)*1.4

W Column =IF(AND(K2="Regular 2nd Shift Attendance Penalty",E2="TEMPRILEY2nd"),(H2/2/1.38)-0.75)*1.38

X Column =IF(AND(K2="Regular Shift 2",E2="TEMPTRC2ND"),H2)

Y Column =IF(AND(K2="Regular Shift 2",E2="TEMPINT2ND"),H2)

X Column =IF(AND(K2="Regular Shift 2",E2="TEMPRILEY2ND"),H2)
  • Do you have the IFS function available? It's in Excel 365 but may be in the latest release of 2016. It would help if we knew what version of Excel you have. – BillDOe Dec 28 '18 at 21:43
  • Why are you using such weird formulas? ((H2/1.38)-1)*1.38 is H2-1.38 to within round-off error; (H2/1.5)*1.25 is H2*5/6, ((H2/1.5)/1.4)*1.5*1.34 is (H2/1.4)*1.34 which is H2*1.34/1.4 or H2*67/70, etc. – Scott Dec 28 '18 at 21:43
  • 2
    What is "better"? Cramming all this into one very long formula can certainly be done, but is it "better"? What are your criteria for "better"? Spreading the logic out over several cells will make the logic easier to follow and apply changes when required. Think of the person who will inherit this workbook from you. Or think 6 months into the future when you only faintly recall what this is all about. What will be easier to maintain? One huge formula that will take you hours to decipher or the logic neatly arranged in separate cells? – teylyn Dec 28 '18 at 22:28
  • Also, each formula produces an independent result. How do you combine those results? If combining them involves cell references to these formulas, just replace the cell reference with that cell's formula. – fixer1234 Dec 28 '18 at 23:36
  • Could you share the sample data to understand the relationship with Cell values and Formulas!! – Rajesh S Dec 29 '18 at 8:51
1

I agree with teylyn's comment about combining all of this into a single formula. It will be impossible to maintain or troubleshoot. Without analyzing each formula, it does look like many of the formulas are based on the same factors, so it may be possible to reduce the number of formulas by consolidating some of the logic.

That said, if you absolutely, positively need this combined into a single formula, it is straightforward since you aren't doing anything like array formulas. I'm going to assume that all of your formulas are working and doing what you want. You describe using a formula in another column to combine the individual results. It sounds like you may be just adding the results, so perhaps your formula looks like:

=L2+M2+N2+O2+P2+Q2+R2+S2+T2+U2+V2+W2+X2

If your consolidation formula is more complex, that's fine; the same principle applies. I'll use this example to explain the process.

What you do is replace each cell reference with the formula in the referenced cell (excluding the initial equal sign). As an illustration, I'll start with X2 since that has a relatively short formula. Rather than retype all of the formulas, copy and paste like this:

  • Click on cell X2. In the formula window, select everything but the initial equal sign and then click Ctrl+C to copy it to the clipboard.
  • Click Esc to cancel editing the formula.
  • Click on the consolidation cell.
  • In the formula window, select X2 within the formula. Replace it by pasting the formula from the clipboard with Ctrl+V.
  • Click Enter to save the first change.

After this first replacement, the consolidation formula would look like:

=L2+M2+N2+O2+P2+Q2+R2+S2+T2+U2+V2+W2+IF(AND(K2="Regular Shift 2",E2="TEMPRILEY2ND"),H2)

Repeat the process for each of the other cell references in the consolidation formula.

Once all of the formulas have been transferred into the consolidated one, and you verify that it works, it will no longer rely on any of the component formulas, so you can delete them. However, I would urge you not to just delete them because they may simplify your life if you ever need to reconstruct or modify the "beast". At least copy the worksheet to another worksheet to preserve it before deleting the component formulas.

CAVEAT: if your current consolidation formula specifies this as a range, like =SUM(L2:X2), you won't be able to just replace L2 and X2 in the formula. You will need to have an explicit reference to each component cell so that all of their formulas get consolidated.

  • 1
    You did lots of hard work,,, but missing is the sample data to cope up with all. Getting 10. ☺ – Rajesh S Dec 31 '18 at 6:29

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.