Getting rid of #VALUE!

[Paul Sheppard]

Hi

I'm using excel 2000

I have a spreadsheet for calculating Overtime

A = Hourly Rate
B = Single Time Hours
C = Single Time Cost
D = Time and a Half Hours
E = Time and a Half Cost
F = Double Time Hours
G = Double Time Costs
H = Total costs

In columns C,E and G I have the following formulae

C > =IF(B3="","",SUM(B3*A3))
E > =IF(D3="","",SUM(A3*D3)+(D3/2*A3))
G > =IF(F3="","",SUM(F3*2*A3))

which calculate the cost of overtime, but leave the cell blank if n
hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hour
columns I get #VALUE! in column H where the formula is =C3+E3+G3

How can I get rid of the #VALUE!, but keep columns C,E & G showing a
blank

Thanks in advance for any hel

 

[Guest]

Try something like =IF(ISERROR(C3+E3+G3),"",C3+E3+G3)

 

[Guest]

Paul,

Can't say for sure this will work in 2000, as i've not used that for a few
years.

But in column H use the formula

=IF(ISERROR(C3+E3+G3)=TRUE,0,C3+E3+G3)

Should return 0 for any instance of value. Alternatively, Alter your
originally If formauls to put 0 instead of ""

 

[Jim May]

Most solutions I've seen use an If(True,DoX,ElseDoY) formula. More to your
problem, but generally you need to enter something like:

=IF(iserr(YourCurrentFormulaHere),"",YourCurrentFormulaHere)

HTH


"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...

 

[Paul Sheppard]

Hi Gary, Tom & Jim

Thanks for your responses, both got rid of the #VALUE!, however the
did not do what I was looking for, I want column H to return the sum o
C + E + G, without having to enter 0's into the hours columns that ar
blank

example file attache

+-------------------------------------------------------------------
|Filename: Book1.zip
|Download: http://www.excelforum.com/attachment.php?postid=3903
+-------------------------------------------------------------------

 

[Bryan Hessey]

Paul,

If you find nothing better (and I hope you do) you can use Conditiona
format and set the cell (as $0.00) to white font on white backgroun
when cell value is Equal to 0.

Not the best, but useable. You can hold the CTRL key to select al
required cells and Conditional Format en mass.

Hope this helps.

 

[Paul Sheppard]

Hi Bryan

Thanks, I'd thought of that and that will be my last resort, I'd prefer
to sort the formula though

Paul

 

[Bryan Hessey]

as =0+(IF(C3="",0,C3))+(IF(E3="",0,E3))+(IF(G3="",0,G3))

where the 0+ is probably superfluous

 

[Paul Sheppard]

Cheers Bryan

You're a star, and yes the +0 is not required

Paul