Cell to return a specific value if left blank

S

Sdtex

Just a novice, so please bear with me. I'm trying to set up an inventory
system where 1 is the most common amount, yet I would also like the
"quantity" column to reflect the input value if different than 1. Thus I
would like to be able to leave a cell blank and return the value of 1,
otherwise it returns the value I input. Hop that makes sense. I tried the
following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank
cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is
left blank, then return a value of 1, otherwise, return the value input into
A1. Thanks for your help...........
 
M

Marcelo

Hello,

If I have undertood what are you looking for it will run.

first of all you can not have a value and a formula in the same cell, a1 for
instance, in this case we have to use an auxiliar column to help us lets
assume column B as auxiliar, so in b1 use

=if(a1="",1,a1)

Hope this helps.
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Sdtex" escreveu:
 
M

Marcelo

Hello,

If I have undertood what are you looking for it will run.

first of all you can not have a value and a formula in the same cell, a1 for
instance, in this case we have to use an auxiliar column to help us lets
assume column B as auxiliar, so in b1 use

=if(a1="",1,a1)

Hope this helps.
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Sdtex" escreveu:
 
B

Bernard Liengme

Your formula =IF(A1="",1,A1)
And so should these
=A1+ISBLANK(A1)
=A1+(A1<1) ... this is the shortest
best wishes
 
B

Bernard Liengme

Your formula =IF(A1="",1,A1)
And so should these
=A1+ISBLANK(A1)
=A1+(A1<1) ... this is the shortest
best wishes
 
S

Sdtex

Thanks to all who have helped. I'm assuming that all those who have
responded with alternative formula's, also agree with Marcelo. By using this
auxiliary column, I now will have an additional column with a quantity
showing any quantity if other than 1, right. i.e. if I use Col. B as
suggested, I will show the number 5(for example) in both columns A & B,
right? One that I input and one that is read via the formula in column A.
Will this be the case if i use the "ISBLANK" formula, or will I still have a
circular reference error? Thanks again to all!!
 
S

Sdtex

Thanks to all who have helped. I'm assuming that all those who have
responded with alternative formula's, also agree with Marcelo. By using this
auxiliary column, I now will have an additional column with a quantity
showing any quantity if other than 1, right. i.e. if I use Col. B as
suggested, I will show the number 5(for example) in both columns A & B,
right? One that I input and one that is read via the formula in column A.
Will this be the case if i use the "ISBLANK" formula, or will I still have a
circular reference error? Thanks again to all!!
 
J

Jacob Skaria

OR

As you mentioned "One that I input and one that is read via the formula in
column A."

In the formula you are using you can check for blank and consider the
default as 1. using same IF condition////
 
J

Jacob Skaria

OR

As you mentioned "One that I input and one that is read via the formula in
column A."

In the formula you are using you can check for blank and consider the
default as 1. using same IF condition////
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel formula, sum the value cell and if empty cell left blank 0
How do I put a blank value in a cell not using "" in a formula 2
Return Blank from Cell Calculation 2
Vlookup and return sheet name also 2
Function to Return a Date Value Q 4
I want to make One Cell mandatory if another cell has a specific value , can anyone help me? 0
Adding fill to cells only when value is "No" in another cell 1
Nested IF Statements 6

Top