#VALUE ERROR - How to eliminate

  • Thread starter Thread starter Macros The Black
  • Start date Start date
M

Macros The Black

I have a spreadhseet set up for measuring the performance of various
production lines at work.

The spreadsheet is basically a hrs & tonnage record.

I like the spreadsheet to be tidy and not display #N/A or 0's whenever a
calculation is not correct.

As such, i have a formula in cell K5 as follows

k5 =IF(SUM(E5:E7)>0,+SUM(E5:E7),"")

k29==IF(SUM(E29:E31)>0,+SUM(E29:E31),"")

k53==IF(SUM(E53:E55)>0,+SUM(E53:E55),"")

Now i want to provide adaily total of each 3 production lines, which at
present i have

k7 =SUM(K5+K29+K53) - Mondays Total
k10 =SUM(K8+K32+K56+K7) - Tuesdays total
k13 =SUM(K11+K35+K59+K10) - Wednesdays total
etc

Problem : Because i am hiding lots of errors by the IF use, the result in
k7,10,13 is #VALUE.

I would like to remove this error and just to make it blank if no data
exists.

Anyone give me any tips ??

Regards

Macros
 
Here's your starter for 10

=SUM(IF(K5="",0,K5),IF(K29="",0,K29),(IF(K53="",0,K53)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
change you formula from

SUM(K5+K29+K53)

to
=Sum(K5,K29,K53)

there is no reason to have both Sum and use addition operators - however,
sum won't produce #Value in this situation while the addition operators
will.
 
Excellent - many thanks for this - had searched this column for answers like
that but missed them

Kind regards

M
 
Back
Top