What does this formulea mean?

[Waa]

I am new at excel and curious... please help.
The following formulea is used in our purchase orders -- what does i
mean?
=IF(SUM(I39)>0, SUM((I39*I40)+I39),""

 

[Vacuum Sealed]

I am new at excel and curious... please help.
The following formulea is used in our purchase orders -- what does it
mean?
=IF(SUM(I39)>0, SUM((I39*I40)+I39),"")

Hi

It is a logical statement, asking for an outcome governed by two conditions.

Condition one: =
IF I39 is greater than zero (0) then
Multiply the values in (I39 & I40) then
Add that total to the value in I39 again...

eg if I39 = 2 and I40 = 4 then the answer would be 10.

(2x4)+2) = 10 ..... (2x4=8)+2=10)

Condition two: =
If the I39 is Blank, then the result will be blank.

But it could be shortened to this as the first Sum statement is summing
a single cell instead of a range of cells.

=IF(I39="","",SUM((I39*I40)+I39))

So this effectively says:

If I39 is Blank, then your answer cell that has the formula will be blank.
Else
If it contains a number, then it processes the sum equation..

There is no need to include the >0 and the "" in the statement as one
negates the other because if there is no value in the cell it would be
blank, conversely, any value entered in the cell would trigger the event

HTH
Mick.

 

[GS]

Mick,
I'm inclined to write that formula as follows...

=IF(LEN(I39),(I39*I40)+I39,"")
OR
=IF(NOT(I39),"",SUM(I39*I40,I39))

...for better clarity as opposed to checking I39<>"" -OR- I39="",
respectively.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Bruno Campanini]

Waa laid this down on his screen :

I am new at excel and curious... please help.
The following formulea is used in our purchase orders -- what does it
mean?
=IF(SUM(I39)>0, SUM((I39*I40)+I39),"")

It means its author is a stupid man...

=IF(I39>0,I39*(1+I40))

Bruno

 

[Bruno Campanini]

Waa laid this down on his screen :

It means its author is a stupid man...

=IF(I39>0,I39*(1+I40))

Bruno

Sorry...

=IF(I39>0,I39*(1+I40),"")

Bruno

 

[Chieftain of the Carpet Crawlers]

Sorry...

=IF(I39>0,I39*(1+I40),"")

Bruno

What does that make you?

 

[Spencer101]

It means its author is a stupid man...

=IF(I39>0,I39*(1+I40))

Bruno[/i]

Sorry...

=IF(I39>0,I39*(1+I40),"")

Bruno
[/i]

[/QUOTE]
What does that make you?[/QUOTE]

OOOoooof! That's got to suck, Bruno!
Perhaps now you'll think twice before replying with something so smug
:

 

[Vacuum Sealed]

Sorry...

=IF(I39>0,I39*(1+I40),"")

Bruno

Bruno

People are here to help others, not debase, simply lay down your spin on
what you think will help the poster solve an issue and move on.

Perfection is one of life's biggest flaws, remember that when slagging
off someone who may know less than yourself...

 

[Bruno Campanini]

Spencer101 explained on 06-07-12 :

It means its author is a stupid man...

=IF(I39>0,I39*(1+I40))

Bruno[/i]

Sorry...

=IF(I39>0,I39*(1+I40),"")

Bruno
[/i]

What does that make you?[/QUOTE]

OOOoooof! That's got to suck, Bruno!
Perhaps now you'll think twice before replying with something so smug!
[/QUOTE]

Are you joking?

What is this formula:
=IF(SUM(I39)>0, SUM((I39*I40)+I39),"")

It makes the very same things of my formula:
=IF(I39>0,I39*(1+I40),"")

with the difference that you don't find here
some ridicolous things like Sum(I139)...

Sum(I139) = I139 even in your poor mathematics, etc.

Try to switch on your brain before speaking, may be
it can help, may be not.

Bruno

 

[joeu2004]

Try to switch on your brain before speaking

Look in the mirror when you say that.

Are you joking? What is this formula:
=IF(SUM(I39)>0, SUM((I39*I40)+I39),"")

It makes the very same things of my formula:
=IF(I39>0,I39*(1+I40),"")

And what if I39 contains the null string ("")? (Or any other text, for that
matter.)

Rhetorical question. The point is: I agree that SUM((I39*I40)+I39) is
silly. However, SUM(I39)>0 is one way of writing effectively
AND(I39<>"",I39>0) or AND(ISNUMBER(I39),I39>0).

I prefer to use N(I39)>0 instead of SUM(I39)>0. But the N function help
page is difficult to find. So I can imagine few people know about it.

The real point is: try being polite, and stop calling people and things
they do "stupid". Such ad hominem attacks are unwarranted; and they have a
tendency to come back and bite you.

 

[Bruno Campanini]

joeu2004 explained :

Look in the mirror when you say that.




And what if I39 contains the null string ("")? (Or any other text, for that
matter.)

If I39 contains Null string or any other text, I39>0 returns False or 0
in the very same way SUM(I39)>0 does.
With the difference it is not ridicolous.
Why don't you try the things before commenting?

Rhetorical question. The point is: I agree that SUM((I39*I40)+I39) is
silly. However, SUM(I39)>0 is one way of writing effectively
AND(I39<>"",I39>0) or AND(ISNUMBER(I39),I39>0).

I prefer to use N(I39)>0 instead of SUM(I39)>0. But the N function help page
is difficult to find. So I can imagine few people know about it.

The real point is: try being polite, and stop calling people and things they
do "stupid". Such ad hominem attacks are unwarranted; and they have a
tendency to come back and bite you.

The real point for me is that I can't avoid to call "stupid" who writes
such stupid things like SUM(I39) or, worse, SUM((I39*I40)+I39)!
If you really need to avoid any ambiguity - but now it is not the case
- use the Unary Operator (--I39).

Bruno

 

[joeu2004]

The real point for me is that I can't avoid to call
"stupid" who writes such stupid things

Fine. Then you won't mind my comments below. "People who live in glass
houses ...".

[EDIT] Okay, I cleaned them up. I should stoop to your level.

If I39 contains Null string or any other text, I39>0 returns
False or 0 in the very same way SUM(I39)>0 does. [....]
Why don't you try the things before commenting?

ROTFL! Right back at you!

When someone tells you something, you are obliged to try it or ask for help
before repeating your own misunderstandings.

You probably don't even know what a null string is. One way to write it is
="". Of course, the more common situation is an IF expression like the one
we wrote here. In any case, the point is: the null string is not the same
as an empty cell (a cell with no constant and no formula).

Put ="" into I39. Then try =IF(I39>0,(I39*I40)+I39,"").

If you don't get a #VALUE error, you are making another mistake, which would
not surprise me at all.

The #VALUE error occurs because you are calculating ""*I40+"". Generally,
we cannot use text in arithmetic expressions, unless Excel recognizes the
text as something it can convert to a number (numeric strings, dates,
percentages, etc). Sadly, Excel does not recognize the null string as an
"empty number". (I think it should.)

Then if you have Excel 2003 or 2007, you can RTFM: find the help page for
"default sort order". It will tell you that all text is consider "greater
than" all numbers. That is why "">0 is TRUE.

(AFAIK, there is no option to alter that behavior. But if there is and you
set it, please let me what it is.)

Now try =IF(SUM(I39)>0,(I39*I40)+I39,""), or as I prefer:
=IF(N(I39)>0,(I39*I40)+I39,""). The result should be the null string.

Explanation.... With SUM(I39) and N(39), the result is zero because SUM
ignores text and N returns zero, even text that Excel might otherwise
recognize as a number.

PS: I wrote previous that the N function help page is hard to find. That
was the case with Excel 2003. Surprisingly, it is straight-forward to find
with Excel 2010, as well as with Excel 2007. I say "surprisingly" because
Excel 2010 help search is mostly broken, IMHO. I usually have to revert to
Excel 2003 or 2007 to find help pages.

If you really need to avoid any ambiguity - but now it
is not the case - use the Unary Operator (--I39).

That will have the same problem: it will return a #VALUE error if I39 is
the null string (or any text that Excel does not recognize as a number).
And for the same reason, to wit: we cannot use (non-numeric) text in
arithmetic expression. FYI, the double negative is an arithmetic
expression.

 

[Bruno Campanini]

joeu2004 has brought this to us :

ROTFL! Right back at you!

When someone tells you something, you are obliged to try it or ask for help
before repeating your own misunderstandings.

You probably don't even know what a null string is. One way to write it is
="". Of course, the more common situation is an IF expression like the one
we wrote here. In any case, the point is: the null string is not the same
as an empty cell (a cell with no constant and no formula).

Put ="" into I39. Then try =IF(I39>0,(I39*I40)+I39,"").

If you don't get a #VALUE error, you are making another mistake, which would
not surprise me at all.

The #VALUE error occurs because you are calculating ""*I40+"". Generally, we
cannot use text in arithmetic expressions, unless Excel recognizes the text
as something it can convert to a number (numeric strings, dates, percentages,
etc). Sadly, Excel does not recognize the null string as an "empty number".
(I think it should.)

Then if you have Excel 2003 or 2007, you can RTFM: find the help page for
"default sort order". It will tell you that all text is consider "greater
than" all numbers. That is why "">0 is TRUE.

(AFAIK, there is no option to alter that behavior. But if there is and you
set it, please let me what it is.)

Now try =IF(SUM(I39)>0,(I39*I40)+I39,""), or as I prefer:
=IF(N(I39)>0,(I39*I40)+I39,""). The result should be the null string.

You are correct.
I shouldn't imagine somebody would write text in cell(s) involved in
mathematical computation.
Then, why not to take into account number in I39 and text in I40?

======================================================
In such a circumstance your function fails as well! |
======================================================

It's so easy to make a mistake...

Finally, can we agree on that?

=IF(N(I39)>0,I39*(1+N(I40)),"")

Bruno

 

[joeu2004]

You are correct.

Well, I'm glad you finally learned something. If only you were man enough
to apologize for your previous insinuations ("Why don't you try the things
before commenting?").

I shouldn't imagine somebody would write text in cell(s)
involved in mathematical computation.

You did that yourself, to wit: =IF(N(I39)>0,I39*(1+N(I40)),""). If that is
in I41, what do you think will happen in if another cell has the formula
=I41/I42 ?

Rhetorical question. The point is: this is quite common in templates as
well as many other situations. It does not surprise me that you lack the
ability and experience to imagine it.

Then, why not to take into account number in I39 and text in I40?

Perhaps the author of the code knows that if I39 meets the condition(s)
tested, I40 is never text. There is nothing wrong with that, IMHO; but I
know that some purists insist on making every bit of code bulletproof. It's
a judgment call.

Finally, can we agree on that?
=IF(N(I39)>0,I39*(1+N(I40)),"")

There is nothing wrong with that. But I am not going dignify your previous
outbursts by allying myself with the likes of you.

 

[Bruno Campanini]

joeu2004 presented the following explanation :

[...]

Perhaps the author of the code knows that if I39 meets the condition(s)
tested, I40 is never text. There is nothing wrong with that, IMHO; but I
know that some purists insist on making every bit of code bulletproof. It's
a judgment call.

Well, when I missed to consider I39 can contain a text, that's my
fault.
When you did the very same thing with I40 "Perhaps the author ..., I40
in never text".

Poor man!

I switch off the thread, save your ink! I will not read you any longer.

Bruno