Formula Assistance
 

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),"")

--
Message posted via http://www.officekb.com

Formula Assistance
 

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 "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 false". Your IF does not appear to contain that
logical test.

Formula Assistance
 

Well put Dave.......well put.....

I "wuzgunna" say that......but my tongue got in front of my eye-tooth and I
couldn't see what I was saying <g

Vaya con Dios,
Chuck, CABGx3




"Dave O" wrote in message
oups.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 "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 false". Your IF does not appear to contain that
logical test.

Formula Assistance
 

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:
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 "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 false". Your IF does not appear to contain that
logical test.

--
Message posted via http://www.officekb.com

Formula Assistance
 

Maybe

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

--
Regards,

Peo Sjoblom

Portland, Oregon




"Chris Cred via OfficeKB.com" <u17747@uwe wrote in message
news:5a9e9b49b12c8@uwe...
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:
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 "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 false". Your IF does not appear to contain that
logical test.

--
Message posted via http://www.officekb.com

Formula Assistance
 

Very 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:
Maybe

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

Dave-

[quoted text clipped - 22 lines]
if the logical test is false". Your IF does not appear to contain that
logical test.

--
Message posted via http://www.officekb.com

Formula Assistance
 

I assume the blanks are from a formula with "" as opposed to empty? Anyway
since SUM ignores text (that is what "" is) you can use something like


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

--
Regards,

Peo Sjoblom

Portland, Oregon




"Chris Cred via OfficeKB.com" <u17747@uwe wrote in message
news:5a9ed550321bc@uwe...
Very 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:
Maybe

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

Dave-

[quoted text clipped - 22 lines]
if the logical test is false". Your IF does not appear to contain that
logical test.

--
Message posted via http://www.officekb.com

Formula Assistance
 

Peo, you are the Man!

Thanks so much. That worked! So let me understand this for future reference.
Since cells that contain formulas with "" (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:
I assume the blanks are from a formula with "" as opposed to empty? Anyway
since SUM ignores text (that is what "" is) you can use something like

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

Very Nice...that was it! Thank You.
Another question reagrding the same formula... I would like to subtract
[quoted text clipped - 16 lines]
if the logical test is false". Your IF does not appear to contain that
logical test.

--
Message posted via http://www.officekb.com