Sum in Excel - blank if = 0

M

MsGinger

Registered User
Messages
330
Hi all,

My head is wrecked today trying to do this.

I want to allocate wages on a cost by site basis.

E.g. Employee 1
€100 gross wages
60% site A, 40% site b

No problem for that, but because I have multiple sites (Sites a - f) and different employees on each site, when I try to get a value of zero or blank for Employee 1 on site c, it returns #VALUE!, so I can't add the column.

Is there a formula you can use to display a blank cell, if the result is zero? Or alternatively only display cell if value is €0.01 or more?

I would be very grateful for any help,
Ginger
 
What formula are you using to do this? If you have 0% entered as the site cost allocation for Employee 1 on site c, then this should give you €0 as the formula result.
Best to give details though so as to get an accurate remedy.
 
I am using the following formula:
1) =sum(b4*'Sheet1'!K3)

Sheet1 k3 is blank as I am using this formula
2) =if(b3=0,"",B3/j3) - this gives a blank cell

for Formula 1 I am getting #VALUE!

Prior to this I had formula 2 as =sum(b3/j3) - this gave me DIV/0 when I used formula 1, so I still couldn't add the column, so I changed it to formula 2 above which displays a blank cell, so I thought that that would = 0?
 
Try this to get rid of those pesky #VALUE!s

=IF(ISERROR(A1), "", A1)
 
Try changing formala 2 to: =if(b3=0,0,B3/j3)
It's the blank value of "" that's causing the error.
 
None of those formula makes much sense. Why are you using the "sum" function at all? For example,
=sum(b3/j3)
Click to expand...
is the exact same as just "=b3/j3". If j3 is blank or zero you're going to get a division by zero error. What are you actually trying to do? You generally use "sum" to add a sequence of values. I think you're making some very basic mistake here; "papering over the cracks" using 'if(iserror(a1),"", A1)' is not the way to go, in my opinion. It makes no sense to divide by zero; if this occurs in your model, then you're probably thinking about the problem in the wrong way.
 
Why not just run a cost centre report from your wages software?
 
Thanks guys,

Lemeister, your suggestion has worked, so thanks very much for that.

Darag, thanks for your input, I'm aware that =b3/j3 is the same as =sum(b3/j3).

What I am trying to do is take the total employee cost (gross pay, employer's PRSI & pension) and allocate a cost per employee per site. We have approximately 8 sites around Dublin, most of our employees move between sites during the week depending on what tasks need to be done.

So using this example, I could have a guy who spends 20% of this time on sites a, b & c, 40% of his time on site d, and 0% of his time on sites e & f, so for a gross wage of €100, €20 each would go against site a, b & c, €40 against site d and zero against site e & f. (at this stage I would like to point out that we do pay more than €100/week but this is just for eg purposes!).

umpo3p!sdn, I can run a cost centre report, but it means manually going into each employee in each week's payroll and allocating a % of their time by site. I want to cut this out and use the information I already have to calculate this once I enter the gross pay on the spreadsheet.

Thanks for your time, I have a couple of other small teething problems with it, but I think I can sort it now.

Cheers,
Ginger
 
You must log in or register to reply here.
Menu
Log in
Register