IF'S Blah

[phuser]

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K16>0),"",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]

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]

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]

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

 

[Franz Verga]

Nel post *phuser* ha scritto:

=IF(ISERROR(F16-K16),"",IF(SUM(F16-K16>0),"",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]

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]

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]

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]

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]

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]

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'!L20>0,('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.

 

[Franz Verga]

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'!L20>0,('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]

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]

Nel post *phuser* ha scritto:


[cut]

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

 

[phuser]

http://www.savefile.com/files/8774554

Once you open the file you will see I created some hidden columns with a
value to get the formula to work, I would most like
to get rid of those. BTW G16 is using exactly the same formula, it had a
value in the "WA" column but nothing in the "In" so I started using that
one, instead of the 1st row which is G7

Nel post *phuser* ha scritto:


[cut]

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