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, if the value in cell I39 is greater than zero, multiply the value in I39 by the value in I40 and add to that the value in I39. If I39 is zero, do nothing.

On 3/07/2012 2:43 PM, Waa wrote:
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.

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

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(I390,I39*(1+I40))

Bruno

on 04-07-12, Bruno Campanini supposed :
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(I390,I39*(1+I40))

Bruno

Sorry...

=IF(I390,I39*(1+I40),"")

Bruno

On Wed, 04 Jul 2012 21:55:46 +0200, Bruno Campanini
wrote:

on 04-07-12, Bruno Campanini supposed :
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(I390,I39*(1+I40))

Bruno

Sorry...

=IF(I390,I39*(1+I40),"")

Bruno

What does that make you?

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

On 5/07/2012 5:55 AM, Bruno Campanini wrote:
on 04-07-12, Bruno Campanini supposed :
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(I390,I39*(1+I40))

Bruno

Sorry...

=IF(I390,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...

Spencer101 explained on 06-07-12 :
Chieftain of the Carpet Crawlers;1603427 Wrote:

It means its author is a stupid man...

=IF(I390,I39*(1+I40))

Bruno

Sorry...

=IF(I390,I39*(1+I40),"")

Bruno

What does that make you?[/i][/color]

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

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(I390,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

"Bruno Campanini" wrote:
Try to switch on your brain before speaking

Look in the mirror when you say that.


"Bruno Campanini" wrote:
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(I390,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<"",I390) or AND(ISNUMBER(I39),I390).

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.

joeu2004 explained :
"Bruno Campanini" wrote:
Try to switch on your brain before speaking

Look in the mirror when you say that.


"Bruno Campanini" wrote:
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(I390,I39*(1+I40),"")

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

If I39 contains Null string or any other text, I390 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<"",I390) or AND(ISNUMBER(I39),I390).

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

"Bruno Campanini" wrote:
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.


"Bruno Campanini" wrote:
If I39 contains Null string or any other text, I390 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(I390,(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.


"Bruno Campanini" wrote:
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.

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(I390,(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

"Bruno Campanini" wrote:
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?").


"Bruno Campanini" wrote:
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.


"Bruno Campanini" wrote:
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.


"Bruno Campanini" wrote:
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.

joeu2004 presented the following explanation :

[...]
"Bruno Campanini" wrote:
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.

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