Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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+D151)*((D14+D15)/-2),(D16+D171)*((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 . |
#3
|
|||
|
|||
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." skrev i melding ... 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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+D151)*((D14+D15)/-2),(D16+D171)*((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 . |
#6
|
|||
|
|||
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." skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
I want a formula to ignore text eg 5mts * 5 ignoring the mts any . | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Using color to ignore cells in a formula????? | Excel Discussion (Misc queries) |