USING IGNORE BLANKS IN FORMULA

Hi,
I want to use the following formula at the bottom of a column of inputs:

=IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,((D16+D17)/-2),"") etc.
etc.

This formula makes sure that if various cell entries in a column are at
least 2 items in that cell, they will be counted as two per package plus a
fraction (thus the divisor /-2). As long as a) the sum of the cells is at
least two and b) there are no blank cells, the formula works. If however, one
of the cells is blank and the total is less than 2 an error message occurs
#value! . I have tried using IGNORE BLANKS in data validation but it does
not affect the error message occuring.

Anyone know if IGNORE BLANKS should work with this? I assume you select all
the cells in the column including the summation cell, true? Also, is the
option ANY VALUE the correct criteria or should I reset it to whole numbers?

Thanks to anyone watching.

Rogerh
[email protected]

Hi!

The #VALUE! error return has nothing to do with data
validation. Either:

Change your current formula to:

=IF((D14+D15)>1,((D14+D15)/-2),0)+IF((D16+D17)>1,
((D16+D17)/-2),0)

Or, maybe use this instead:

=SUM((D14+D15>1)*((D14+D15)/-2),(D16+D17>1)*((D16+D17)/-2))

Biff

>-----Original Message-----
>Hi,
>I want to use the following formula at the bottom of a
column of inputs:
>
>=IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,
((D16+D17)/-2),"") etc.
>etc.
>
>This formula makes sure that if various cell entries in a
column are at
>least 2 items in that cell, they will be counted as two
per package plus a
>fraction (thus the divisor /-2). As long as a) the sum
of the cells is at
>least two and b) there are no blank cells, the formula
works. If however, one
>of the cells is blank and the total is less than 2 an
error message occurs
>#value! . I have tried using IGNORE BLANKS in data
validation but it does
>not affect the error message occuring.
>
>Anyone know if IGNORE BLANKS should work with this? I
assume you select all
>the cells in the column including the summation cell,
true? Also, is the
>option ANY VALUE the correct criteria or should I reset
it to whole numbers?
>
>Thanks to anyone watching.
>
>Rogerh
>[email protected]
>.
>

Hi Roger

Your IFs return "", which is an emtry string, not a number, not an emtru
cell. You can not to math with those things.
So learn to live with a zero once in a while:
=IF((D14+D15)>1,((D14+D15)/-2),0)+IF((D16+D17)>1,((D16+D17)/-2),0)

HTH. Best wishes Harald

"Roger H." <[email protected]> skrev i melding
news:[email protected]...
> Hi,
> I want to use the following formula at the bottom of a column of inputs:
>
> =IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,((D16+D17)/-2),"") etc.
> etc.
>
> This formula makes sure that if various cell entries in a column are at
> least 2 items in that cell, they will be counted as two per package plus a
> fraction (thus the divisor /-2). As long as a) the sum of the cells is
at
> least two and b) there are no blank cells, the formula works. If however,
one
> of the cells is blank and the total is less than 2 an error message occurs
> #value! . I have tried using IGNORE BLANKS in data validation but it
does
> not affect the error message occuring.
>
> Anyone know if IGNORE BLANKS should work with this? I assume you select
all
> the cells in the column including the summation cell, true? Also, is the
> option ANY VALUE the correct criteria or should I reset it to whole
numbers?
>
> Thanks to anyone watching.
>
> Rogerh
> [email protected]

THANKS FOR THE TIPS, SOLVED MY PROBLEM!
ROGER



"Roger H." wrote:

> Hi,
> I want to use the following formula at the bottom of a column of inputs:
>
> =IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,((D16+D17)/-2),"") etc.
> etc.
>
> This formula makes sure that if various cell entries in a column are at
> least 2 items in that cell, they will be counted as two per package plus a
> fraction (thus the divisor /-2). As long as a) the sum of the cells is at
> least two and b) there are no blank cells, the formula works. If however, one
> of the cells is blank and the total is less than 2 an error message occurs
> #value! . I have tried using IGNORE BLANKS in data validation but it does
> not affect the error message occuring.
>
> Anyone know if IGNORE BLANKS should work with this? I assume you select all
> the cells in the column including the summation cell, true? Also, is the
> option ANY VALUE the correct criteria or should I reset it to whole numbers?
>
> Thanks to anyone watching.
>
> Rogerh
> [email protected]

Hi Biff,
Tip worked fine with zero. Thank you!



"Biff" wrote:

> Hi!
>
> The #VALUE! error return has nothing to do with data
> validation. Either:
>
> Change your current formula to:
>
> =IF((D14+D15)>1,((D14+D15)/-2),0)+IF((D16+D17)>1,
> ((D16+D17)/-2),0)
>
> Or, maybe use this instead:
>
> =SUM((D14+D15>1)*((D14+D15)/-2),(D16+D17>1)*((D16+D17)/-2))
>
> Biff
>
> >-----Original Message-----
> >Hi,
> >I want to use the following formula at the bottom of a
> column of inputs:
> >
> >=IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,
> ((D16+D17)/-2),"") etc.
> >etc.
> >
> >This formula makes sure that if various cell entries in a
> column are at
> >least 2 items in that cell, they will be counted as two
> per package plus a
> >fraction (thus the divisor /-2). As long as a) the sum
> of the cells is at
> >least two and b) there are no blank cells, the formula
> works. If however, one
> >of the cells is blank and the total is less than 2 an
> error message occurs
> >#value! . I have tried using IGNORE BLANKS in data
> validation but it does
> >not affect the error message occuring.
> >
> >Anyone know if IGNORE BLANKS should work with this? I
> assume you select all
> >the cells in the column including the summation cell,
> true? Also, is the
> >option ANY VALUE the correct criteria or should I reset
> it to whole numbers?
> >
> >Thanks to anyone watching.
> >
> >Rogerh
> >[email protected]
> >.
> >
>

Harold,
Good information for the future. Zero works fine. Thank you.
Roger H



"Harald Staff" wrote:

> Hi Roger
>
> Your IFs return "", which is an emtry string, not a number, not an emtru
> cell. You can not to math with those things.
> So learn to live with a zero once in a while:
> =IF((D14+D15)>1,((D14+D15)/-2),0)+IF((D16+D17)>1,((D16+D17)/-2),0)
>
> HTH. Best wishes Harald
>
> "Roger H." <[email protected]> skrev i melding
> news:[email protected]...
> > Hi,
> > I want to use the following formula at the bottom of a column of inputs:
> >
> > =IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,((D16+D17)/-2),"") etc.
> > etc.
> >
> > This formula makes sure that if various cell entries in a column are at
> > least 2 items in that cell, they will be counted as two per package plus a
> > fraction (thus the divisor /-2). As long as a) the sum of the cells is
> at
> > least two and b) there are no blank cells, the formula works. If however,
> one
> > of the cells is blank and the total is less than 2 an error message occurs
> > #value! . I have tried using IGNORE BLANKS in data validation but it
> does
> > not affect the error message occuring.
> >
> > Anyone know if IGNORE BLANKS should work with this? I assume you select
> all
> > the cells in the column including the summation cell, true? Also, is the
> > option ANY VALUE the correct criteria or should I reset it to whole
> numbers?
> >
> > Thanks to anyone watching.
> >
> > Rogerh
> > [email protected]
>
>
>