ExcelBanter - IF'S Blah

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - IF'S Blah (https://www.excelbanter.com/excel-worksheet-functions/95264-ifs-blah.html)

phuser

IF'S Blah

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K160),"",SUM(F16-K16)))

Cells F16 & K16 can either contain a "Null String" ("") or a number created
from another IF statement , from these 2 Cells I need another calculation,
if the error "#Value" is made I want it to be blank, If the number is 0, I
want it to be blank, but if it's a number I want it to appear.

Any help is greatly appreciated

VBA Noob

IF'S Blah

Hi phuser,

Think you nearly had it. Should be

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K16<=0),"",SUM(F16-K16)))

< = less than. I've added = so it also shows as blank.

Thanks

VBA Noob

--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=554122

VBA Noob

IF'S Blah

VBA Noob

IF'S Blah

Franz Verga

IF'S Blah

Nel post
*phuser* ha scritto:

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K160),"",SUM(F16-K16)))

Cells F16 & K16 can either contain a "Null String" ("") or a number
created from another IF statement , from these 2 Cells I need another
calculation, if the error "#Value" is made I want it to be blank, If
the number is 0, I want it to be blank, but if it's a number I want
it to appear.
Any help is greatly appreciated

I don't know I have understood what you mean...

Try with this one:

=IF(and(F16="",K16=""),"",IF((F16-K16)0,"",F16-K16))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Dave

IF'S Blah

Franz

I'm just trying to learn by following this group. You all have been so much
help already. Could you please explain why this formula does not work as a
solution for the the OP's problem

=IF(OR(ISERROR(F16-K16),((F16-K16)<0)),"",F16-K16)

Dave

"Franz Verga" wrote in message
...
Nel post
*phuser* ha scritto:

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K160),"",SUM(F16-K16)))

Cells F16 & K16 can either contain a "Null String" ("") or a number
created from another IF statement , from these 2 Cells I need another
calculation, if the error "#Value" is made I want it to be blank, If
the number is 0, I want it to be blank, but if it's a number I want
it to appear.
Any help is greatly appreciated

I don't know I have understood what you mean...

Try with this one:

=IF(and(F16="",K16=""),"",IF((F16-K16)0,"",F16-K16))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Franz Verga

IF'S Blah

Nel post
*Dave* ha scritto:

Franz

I'm just trying to learn by following this group. You all have been
so much help already. Could you please explain why this formula does
not work as a solution for the the OP's problem

=IF(OR(ISERROR(F16-K16),((F16-K16)<0)),"",F16-K16)

Dave

I think because he wants to see a null string ("") also if (F16-K16)0, so
you could modify your formula in this way:

=IF(OR(ISERROR(F16-K16),((F16-K16)0)),"",F16-K16)

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

phuser

IF'S Blah

Well Guys, I so much appreciate all the help, but something is missing when
I try, VBA Noob's only worked if the both cells were blank or both contained
numbers but if 1 of the cells contained a whole number and 1 was blank it
returned a blank cell. Need it to be like 1-0=1 right, not 1-0=0
Franz formula returned a #Value, when both cells were blank as did David's

"Dave" wrote in message
...
Franz

I'm just trying to learn by following this group. You all have been so
much help already. Could you please explain why this formula does not
work as a solution for the the OP's problem

=IF(OR(ISERROR(F16-K16),((F16-K16)<0)),"",F16-K16)

Dave

"Franz Verga" wrote in message
...
Nel post
*phuser* ha scritto:

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K160),"",SUM(F16-K16)))

Cells F16 & K16 can either contain a "Null String" ("") or a number
created from another IF statement , from these 2 Cells I need another
calculation, if the error "#Value" is made I want it to be blank, If
the number is 0, I want it to be blank, but if it's a number I want
it to appear.
Any help is greatly appreciated

I don't know I have understood what you mean...

Try with this one:

=IF(and(F16="",K16=""),"",IF((F16-K16)0,"",F16-K16))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Dave

IF'S Blah

Franz

You are correct I used less than 0 instead of greater than 0 but if text is
entered in either cell F16 or cell K16 it should generate an error an
therefore display a null string but it does not it displays #value. What am
I missing?

Dave

"Franz Verga" wrote in message
...
Nel post
*Dave* ha scritto:

Franz

I'm just trying to learn by following this group. You all have been
so much help already. Could you please explain why this formula does
not work as a solution for the the OP's problem

=IF(OR(ISERROR(F16-K16),((F16-K16)<0)),"",F16-K16)

Dave

I think because he wants to see a null string ("") also if (F16-K16)0,
so you could modify your formula in this way:

=IF(OR(ISERROR(F16-K16),((F16-K16)0)),"",F16-K16)

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Franz Verga

IF'S Blah

Nel post
*phuser* ha scritto:

Well Guys, I so much appreciate all the help, but something is
missing when I try, VBA Noob's only worked if the both cells were
blank or both contained numbers but if 1 of the cells contained a
whole number and 1 was blank it returned a blank cell. Need it to be
like 1-0=1 right, not 1-0=0 Franz formula returned a #Value, when both
cells were blank as did
David's

Maybe it should be better if could explain again, in a clearer way, what are
the expected results and the conditions...

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

phuser

IF'S Blah

Well I may have sabatoged this thread from the beginning I wanted less than
not greater than, and have put that in
but the results are the same. VBA Noob got it right, it's like he knew what
I wanted before I did ;-)

Ok I'll try to explain it best I can
F7 = IF('062306'!L200,('062306'!L20),"") returns a value of (e.g. 6) or ""
K7 = IF(SUM(I20/D19)0,SUM(I20/D19),"") also returns value of (whole number)
or ""

G7 contains the calculation of those 2 cells, if 1 of the values in F7 or K7
is blank the value will be returned #Value,
so Im trying to eliminate that error by putting in the ISERROR statement,
but I also want it to be blank if less than 0 amount is calculated, in the
end, all I want to see in G7 is a whole number, anything else should return
a blank cell.

"phuser" wrote in message
...
=IF(ISERROR(F16-K16),"",IF(SUM(F16-K160),"",SUM(F16-K16)))

Cells F16 & K16 can either contain a "Null String" ("") or a number
created from another IF statement , from these 2 Cells I need another
calculation, if the error "#Value" is made I want it to be blank, If the
number is 0, I want it to be blank, but if it's a number I want it to
appear.

Any help is greatly appreciated

Franz Verga

IF'S Blah

Nel post
*phuser* ha scritto:

Well I may have sabatoged this thread from the beginning I wanted
less than not greater than, and have put that in
but the results are the same. VBA Noob got it right, it's like he
knew what I wanted before I did ;-)

Ok I'll try to explain it best I can
F7 = IF('062306'!L200,('062306'!L20),"") returns a value of (e.g.
6) or "" K7 = IF(SUM(I20/D19)0,SUM(I20/D19),"") also returns value
of (whole number) or ""

G7 contains the calculation of those 2 cells, if 1 of the values in
F7 or K7 is blank the value will be returned #Value,
so Im trying to eliminate that error by putting in the ISERROR
statement, but I also want it to be blank if less than 0 amount is
calculated, in the end, all I want to see in G7 is a whole number,
anything else should return a blank cell.

So now, maybe (maybe) I have understood...

Try this one, fitting the ranges:

=IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16))

or also

=IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16)

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

phuser

IF'S Blah

Something just isnt adding up right, if there is a number in F7 and K7 is
blank, G7 still returns a blank cell, I dont get it.

"Franz Verga" wrote in message
...
Nel post
*phuser* ha scritto:

Well I may have sabatoged this thread from the beginning I wanted
less than not greater than, and have put that in
but the results are the same. VBA Noob got it right, it's like he
knew what I wanted before I did ;-)

Ok I'll try to explain it best I can
F7 = IF('062306'!L200,('062306'!L20),"") returns a value of (e.g.
6) or "" K7 = IF(SUM(I20/D19)0,SUM(I20/D19),"") also returns value
of (whole number) or ""

G7 contains the calculation of those 2 cells, if 1 of the values in
F7 or K7 is blank the value will be returned #Value,
so Im trying to eliminate that error by putting in the ISERROR
statement, but I also want it to be blank if less than 0 amount is
calculated, in the end, all I want to see in G7 is a whole number,
anything else should return a blank cell.

So now, maybe (maybe) I have understood...

Try this one, fitting the ranges:

=IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16))

or also

=IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16)

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

Franz Verga

IF'S Blah

Nel post
*phuser* ha scritto:

[cut]

G7 contains the calculation of those 2 cells, if 1 of the values in
F7 or K7 is blank the value will be returned #Value,
so Im trying to eliminate that error by putting in the ISERROR
statement, but I also want it to be blank if less than 0 amount is
calculated, in the end, all I want to see in G7 is a whole number,
anything else should return a blank cell.

So now, maybe (maybe) I have understood...

Try this one, fitting the ranges:

=IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16))

or also

=IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16)

Something just isnt adding up right, if there is a number in F7 and
K7 is blank, G7 still returns a blank cell, I dont get it.

Let me understand...

In the above formulas, F16 stands for your real F7 and K16 for your real K7?

Which one of the two formulas did you try?

I wrote the formulas directly in OE, without trying, but now I tried and the
first, i.e.:

=IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16))

works for me...

Maybe you can post an example file on www.savefile.com

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

All times are GMT +1. The time now is 07:33 AM.