Hi all,
I've been stuck on an Excel problem for a few hours now, I'm basically creating a template cost sheet for my company but on one of the sheets that calculates machining hours and costs I cannot get a column to add all of the other columns up.
Basically (trying to keep this as simple as possible) each line has up to 4 machining operations as follows:
At the end of each line I need a total box, however as the user could leave some cells empty I need the 'total' formula to work regardless of whether cells are populated.
I've added an excerpt from the sheet below which will hopefully get across what I'm doing, I can send this via email if required too.
I can get one set of operations to work fine by using this formula:
=IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))
The question is how do I extend this to the other operation groups? Every time I try basically extending this formula it gives me the too many arguments error.
I'd greatly appreciate anyones assistance with this before I pull my hair out.
Thanks in advance
<tbody>
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col></colgroup>
I've been stuck on an Excel problem for a few hours now, I'm basically creating a template cost sheet for my company but on one of the sheets that calculates machining hours and costs I cannot get a column to add all of the other columns up.
Basically (trying to keep this as simple as possible) each line has up to 4 machining operations as follows:
- Each operation consists of an operation name in the first column selected from a dropdown list
- The second column is the hourly rate which is auto populated from the first column depending on the operation via a lookup formula
- The third column is manual entry, and is the amount of machining hours
- The fourth column is something I've just added which is where my problems have started, i've just called it manual multiplier for now and it's a manual entry box, essentially it just multiplies the sencond and third column
At the end of each line I need a total box, however as the user could leave some cells empty I need the 'total' formula to work regardless of whether cells are populated.
I've added an excerpt from the sheet below which will hopefully get across what I'm doing, I can send this via email if required too.
I can get one set of operations to work fine by using this formula:
=IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))
The question is how do I extend this to the other operation groups? Every time I try basically extending this formula it gives me the too many arguments error.
I'd greatly appreciate anyones assistance with this before I pull my hair out.
Thanks in advance
Operation 1 | Operation 2 | Operation 3 | Operation 4 | |||||||||||||||
Line | Part Description | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Total In-Process Cost |
1 | Boring | £77.94 | 10 | 10 | Fitting | £48.64 | 10 | 10 | Boring | £77.94 | 10 | 10 | Boring | £77.94 | 10 | 10 | £7,794 | |
2 |
<tbody>
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col></colgroup>