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:
 
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!!
 
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

VBA Coding Help for Beginner 0
If Not blank - Formula for two cells 0
Excel 2013: How to ensure a cell is not left blank by users? (Not using VBA) 0
Change the colour of a neighbouring cell 6
Nested if statements creating a blank cell 1
Calculate the median value before a specified list 0
VB code. If statement between two values 1
Vlookup and return sheet name also 2

Top