Formula Assistance

[Chris Cred via OfficeKB.com]

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

 

[Dave O]

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.

 

[CLR]

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

 

[Chris Cred via OfficeKB.com]

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)

 

[Peo Sjoblom]

Maybe

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

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Chris Cred via OfficeKB.com]

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

Maybe

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

Dave-

[quoted text clipped - 22 lines]

 

[Peo Sjoblom]

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

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

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.

 

[Chris Cred via OfficeKB.com]

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?

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]