software

Hey All-

I was wondering if anyone could help me with this formula. The syntax is
incorrect, however, this is what I would like the calculations to accomplish.
I'm aware SUM means SUM, however, I need a way to subtract those cells that
are listed. Maybe I've been looking at this formula too long since I'm
stumped.

Thanks in advance for any help you can offer!
Chris

Formula

=IF(SUM(G24:G91-G87:G90)/SUM(F24:F91-G87:G90),SUM(G24:G91-G87:G90)/SUM(F24:
F91-G87:G90),quot;quot;)

--
Message posted via www.officekb.com

This part:
SUM(G24:G91-G87:G90)
.... should look like SUM(G24:G91)-SUM(G87:G90)

Also, recall that the first part of an IF statement is a logical test-
so the formula needs to say quot;If sum(this, that, the other) is equal to
that, return a value if the logical test is true, return another value
if the logical test is falsequot;. Your IF does not appear to contain that
logical test.Well put Dave.......well put.....

I quot;wuzgunnaquot; say that......but my tongue got in front of my eye-tooth and I
couldn't see what I was saying lt;ggt;

Vaya con Dios,
Chuck, CABGx3

quot;Dave Oquot; gt; wrote in message oups.com...
gt; This part:
gt; SUM(G24:G91-G87:G90)
gt; ... should look like SUM(G24:G91)-SUM(G87:G90)
gt;
gt; Also, recall that the first part of an IF statement is a logical test-
gt; so the formula needs to say quot;If sum(this, that, the other) is equal to
gt; that, return a value if the logical test is true, return another value
gt; if the logical test is falsequot;. Your IF does not appear to contain that
gt; logical test.
gt;
Dave-

The statement does have all three factors(logical, true, and false) and also
returns a value, however, it is not the correct value.

Anyway, here is what I'm trying to accomplish...

Take the sum of (G24:G91) and subtract G87 and G90, take the sum of (F24:F91)
and subtract F87 and F90, then divide the final result of G by the final
result of Ffor my value.

Here's what I switched my formula to...However, it will only calculate the
first portion and not divide by F.
=SUM(G24:G91)-(G87)/SUM(F24:F91)-(F87)

Dave O wrote:
gt;This part:
gt;SUM(G24:G91-G87:G90)
gt;... should look like SUM(G24:G91)-SUM(G87:G90)
gt;
gt;Also, recall that the first part of an IF statement is a logical test-
gt;so the formula needs to say quot;If sum(this, that, the other) is equal to
gt;that, return a value if the logical test is true, return another value
gt;if the logical test is falsequot;. Your IF does not appear to contain that
gt;logical test.

--
Message posted via www.officekb.com

Maybe

=(SUM(G24:G91)-G87)/(SUM(F24:F91)-F87)

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Chris Cred via OfficeKB.comquot; lt;u17747@uwegt; wrote in message
news:5a9e9b49b12c8@uwe...
gt; Dave-
gt;
gt; The statement does have all three factors(logical, true, and false) and
gt; also
gt; returns a value, however, it is not the correct value.
gt;
gt; Anyway, here is what I'm trying to accomplish...
gt;
gt; Take the sum of (G24:G91) and subtract G87 and G90, take the sum of
gt; (F24:F91)
gt; and subtract F87 and F90, then divide the final result of G by the final
gt; result of Ffor my value.
gt;
gt; Here's what I switched my formula to...However, it will only calculate the
gt; first portion and not divide by F.
gt; =SUM(G24:G91)-(G87)/SUM(F24:F91)-(F87)
gt;
gt; Dave O wrote:
gt;gt;This part:
gt;gt;SUM(G24:G91-G87:G90)
gt;gt;... should look like SUM(G24:G91)-SUM(G87:G90)
gt;gt;
gt;gt;Also, recall that the first part of an IF statement is a logical test-
gt;gt;so the formula needs to say quot;If sum(this, that, the other) is equal to
gt;gt;that, return a value if the logical test is true, return another value
gt;gt;if the logical test is falsequot;. Your IF does not appear to contain that
gt;gt;logical test.
gt;
gt; --
gt; Message posted via www.officekb.comVery Nice...that was it! Thank You.
Another question reagrding the same formula... I would like to subtract G87
and G89 and also F87 and F89 from the ranges, however, when the cells are
left blank it returns a #VALUE error. If G87 and F87 have values and G89 and
F89 are blank it gives the same #VALUE error, but if all cells that I'm
subtracting from have values in them then it is correct. WHat do you
recommend?
Chris
Peo Sjoblom wrote:
gt;Maybe
gt;
gt;=(SUM(G24:G91)-G87)/(SUM(F24:F91)-F87)
gt;
gt;gt; Dave-
gt;gt;
gt;[quoted text clipped - 22 lines]
gt;gt;gt;if the logical test is falsequot;. Your IF does not appear to contain that
gt;gt;gt;logical test.

--
Message posted via www.officekb.com

I assume the blanks are from a formula with quot;quot; as opposed to empty? Anyway
since SUM ignores text (that is what quot;quot; is) you can use something like=(SUM(G24:G91)-SUM(G87,G89))/(SUM(F24:F91)-SUM(F87,F89))

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Chris Cred via OfficeKB.comquot; lt;u17747@uwegt; wrote in message
news:5a9ed550321bc@uwe...
gt; Very Nice...that was it! Thank You.
gt; Another question reagrding the same formula... I would like to subtract
gt; G87
gt; and G89 and also F87 and F89 from the ranges, however, when the cells are
gt; left blank it returns a #VALUE error. If G87 and F87 have values and G89
gt; and
gt; F89 are blank it gives the same #VALUE error, but if all cells that I'm
gt; subtracting from have values in them then it is correct. WHat do you
gt; recommend?
gt; Chris
gt;
gt;
gt;
gt; Peo Sjoblom wrote:
gt;gt;Maybe
gt;gt;
gt;gt;=(SUM(G24:G91)-G87)/(SUM(F24:F91)-F87)
gt;gt;
gt;gt;gt; Dave-
gt;gt;gt;
gt;gt;[quoted text clipped - 22 lines]
gt;gt;gt;gt;if the logical test is falsequot;. Your IF does not appear to contain that
gt;gt;gt;gt;logical test.
gt;
gt; --
gt; Message posted via www.officekb.comPeo, you are the Man!

Thanks so much. That worked! So let me understand this for future reference.
Since cells that contain formulas with quot;quot; (False) opposed to data,
subtracting them is a problem, however, simple addition is not, correct?
Therefore, the formula you wrote should work in senerios as such?

Peo Sjoblom wrote:
gt;I assume the blanks are from a formula with quot;quot; as opposed to empty? Anyway
gt;since SUM ignores text (that is what quot;quot; is) you can use something like
gt;
gt;=(SUM(G24:G91)-SUM(G87,G89))/(SUM(F24:F91)-SUM(F87,F89))
gt;
gt;gt; Very Nice...that was it! Thank You.
gt;gt; Another question reagrding the same formula... I would like to subtract
gt;[quoted text clipped - 16 lines]
gt;gt;gt;gt;gt;if the logical test is falsequot;. Your IF does not appear to contain that
gt;gt;gt;gt;gt;logical test.

--
Message posted via www.officekb.com