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 no
hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hours
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 as
blank

Thanks in advance for any help


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

Try something like =IF(ISERROR(C3+E3+G3),"",C3+E3+G3)
--
Gary's Student


"Paul Sheppard" wrote:


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 no
hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hours
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 as
blank

Thanks in advance for any help


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

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 ""

"Paul Sheppard" wrote:


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 no
hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hours
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 as
blank

Thanks in advance for any help


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

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),"",YourCurrentFo rmulaHere)

HTH


"Paul Sheppard"
wrote in message
news:Paul.Sheppard.1ws4ue_1129104307.4895@excelfor um-nospam.com...

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 no
hours are enterred in B,D & F

The problem I have is that unless I enter an amount in all three hours
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 as
blank

Thanks in advance for any help


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

Hi Gary, Tom & Jim

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

example file attached


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

--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

Paul,

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

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

Hope this helps.



Paul Sheppard Wrote:
Hi Gary, Tom & Jim

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

example file attached


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=475314

Hi Bryan

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

Paul


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314

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

where the 0+ is probably superfluous


Paul Sheppard Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=475314

Cheers Bryan

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

Paul


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=475314