|
Getting rid of #VALUE!
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 |
| All times are GMT +1. The time now is 02:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com