Simple formula that is whooping my tail

[go77377]

Below is 3 different formulas that occur on 3 different rows. Jut a quick summary of what they are. I'm trying to create a labor calculator that when you enter man hours for a job it will total everything. The "S" means straight time, the "O" means overtime, the "D" means double time, if that helps. The numbers in the formula are the labor rates.

Not all of this formula will apply, only what I enter in the columns will be used to do the costing. The formulas work on each separate row as they should but I really need to combine all 3 for consistency throughout the spreadsheet.

I have tried everything to combine the formula and I keep crashing it once I combine them............. any suggestions?


=IF(D8="S",(C8*70),IF(D8="O",(C8*G8105),IF(D8="D",(C8*140))))

=IF(G9="S",(E9*F9*60),IF(G9="O",(E9*F9*90),IF(G9="D",(E9*F9*120))))

=IF(J10="S",(H10*I10*60),IF(J10="O",(H10*I10*90),IF(J10="D",(H10*I10*120))))

 

[cstimart]

Like this???

=IF(D8="S",(C8*70),IF(D8="O",(C8*105),IF(D8="D",(C8*140),IF(G9="S",(E9*F9*60),IF(G9="O",(E9*F9*90),IF(G9="D",(E9*F9*120),IF(J10="S",(H10*I10*60),IF(J10="O",(H10*I10*90),IF(J10="D",(H10*I10*120))))))))))

 

[go77377]

Basically that is what I'm trying to do, but the formula will not work once it is combined. I even tried what you show and it crashes.

Maybe I can explain it a little easier where it makes a little better sense, but that might be a task for me, lol.

The 3 formulas need to run in the same cell, but I need each formula to complete the total as it is shown but do the total of all 3 in one cell.

Does that help?

 

[cstimart]

basically that is what i'm trying to do, but the formula will not work once it is combined. I even tried what you show and it crashes.

Maybe i can explain it a little easier where it makes a little better sense, but that might be a task for me, lol.

The 3 formulas need to run in the same cell, but i need each formula to complete the total as it is shown but do the total of all 3 in one cell.

Does that help?

=(if(d8="s",(c8*70),if(d8="o",(c8*105),if(d8="d",(c8*140),0))))+(if(g9="s",(e9*f9*60),if(g9="o",(e9*f9*90),if(g9="d",(e9*f9*120),0))))+(if(j10="s",(h10*i10*60),if(j10="o",(h10*i10*90),if(j10="d",(h10*i10*120),0))))

 

[go77377]

Below is the image, hopefully this will help make it easier to understand.

Row 8, Row 9 & Row10 formulas work, my goal is to have each formula work in one row. Which will total all the hours, plus all the labor cost, sometimes the shop and the field labor will not be used but at least the formula will be there when it is needed.

Here is the field labor formula below from row 8
=IF(D8="S",(C8*70),IF(D8="O",(C8*G8105),IF(D8="D",(C8*140))))

Here is the field labor from row 9 (the reason there are 2 field labor formulas is because the hourly rate is different
=IF(G9="S",(E9*F9*60),IF(G9="O",(E9*F9*90),IF(G9="D",(E9*F9*120))))

Here is the shop labor formula from row 10
=IF(J10="S",(H10*I10*60),IF(J10="O",(H10*I10*90),IF(J10="D",(H10*I10*120))))

 

[Nalani]

my goal is to have each formula work in one row

What CELL do you want the Total Hours to go into. -- L5 ??
What CELL do you want the Total Cost to go into. -- M5 ??
... Or some other Cell(s)

Do the formulas that you currently have stay in M8:M10 ??

Do you have a formula in L8:L10 or is this manual input ??

-----------

By the way -- a Picture of your sheet does not help us very much. I had to recreate your sheet manually, typing in everything I thought was relevant.

Try one of the downloads in my signature block for posting samples of your sheet. This way your formula will show exactly where they are and it is easy for someone to copy from the Board into Excel to work out a solution.

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">Field Labor</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Field Labor</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Shop Labor</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Job Description</td><td style="text-align: center;;">Total Hours</td><td style="text-align: center;;">Total Labor</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">Lead</td><td style=";">Hours</td><td style=";">Rate</td><td style=";">Helper</td><td style=";">Hours</td><td style=";">Rate</td><td style=";">Helper</td><td style=";">Hours</td><td style=";">Rate</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">s</td><td style="text-align: right;;"></td><td style="text-align: right;;">30</td><td style="text-align: right;;">1900</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">o</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style=";">o</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">d</td><td style="text-align: right;;"></td><td style="text-align: right;;">32</td><td style="text-align: right;;">2280</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style=";">o</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";">s</td><td style="text-align: right;;"></td><td style="text-align: right;;">18</td><td style="text-align: right;;">660</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style=";">o</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">11</td><td style=";">o</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">1470</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">11</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style=";">o</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td><td style="text-align: right;;">1580</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style=";">s</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style=";">s</td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td><td style="text-align: right;;">800</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L8</th><td style="text-align:left">=SUMIF(<font color="Blue">B$7:J$7,"Hours",B8:J8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M8</th><td style="text-align:left">=IF(<font color="Blue">D8="S",(<font color="Red">C8*70</font>),IF(<font color="Red">D8="O",(<font color="Green">C8*G8105</font>),IF(<font color="Green">D8="D",(<font color="Purple">C8*140</font>)</font>)</font>)</font>)+IF(<font color="Blue">G8="S",(<font color="Red">E8*F8*60</font>),IF(<font color="Red">G8="O",(<font color="Green">E8*F8*90</font>),IF(<font color="Green">G8="D",(<font color="Purple">E8*F8*120</font>)</font>)</font>)</font>)+IF(<font color="Blue">J8="S",(<font color="Red">H8*I8*60</font>),IF(<font color="Red">J8="O",(<font color="Green">H8*I8*90</font>),IF(<font color="Green">J8="D",(<font color="Purple">H8*I8*120</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Nalani]

You had better mind reading abilities than me, Sal

I totally missed the "A" column where it looks like "Job No. 1, No. 2, etc"

 

[Sal Paradise]

Maybe I should send you a copy of my mind-reading add-in.

 

[Nalani]

Maybe you should ask the Admin's to make it a "Sticky"