Combining Formulas

[JimS]

I have two columns that get data from two different formulas. The
reason I have two columns is becasue I couldn't figure out how to
combine the two formulas. I worked on it for a while, but I had to
give up and divide it in half.

I'll post the two formulas. Maybe someone can figure out an easy way
to combine them. If you need more info, let me know.

Thanks.


=IF(C3="","",IF(B2="",IF(C3<=0,IF(D3<C3,B4,IF(B5="",IF(C3<=0,IF(D3<C3,B3,IF(B2="",IF(C3<=0,IF(D3>C3,B3,IF(B5="",IF(C3<=0,IF(D3>C3,B3)))))))))))))


=IF(C3="","",IF(C3>0,"",IF(B4="",IF(C3<=0,IF(D3<C3,B2,IF(B4="",IF(C3<=0,IF(D3<C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3))))))))))))))

 

[Niek Otten]

Can you explain in words what type of problem you're trying to solve?

 

[Harlan Grove]

I have two columns that get data from two different formulas.  The
reason I have two columns is becasue I couldn't figure out how to
combine the two formulas.  I worked on it for a while, but I had to
give up and divide it in half.

....
[reformatted -- with comments]

=IF(C3="",

"",
IF(B2="", -- you've now checked whether B2 = "", you
don't need to do it again
IF(C3<=0, -- you've not checked whether C3 <= 0, you
don't need to do it again
IF(D3<C3, -- so D3 < C3 <= 0
B4,

At this point you're returning B4 when C3 <> "", C3 <= 0 and D3 < C3.
But now you enter the ELSE portion of the *INNERMOST* IF so far, which
means C3 still <> "", C3 still <= 0, but now D3 >= C3.

IF(B5="", -- you've now checked whether B5 = "", you
don't need to do it again
IF(C3<=0, -- unnecessary
IF(D3<C3, -- IMPOSSIBLE
....

At this point D3 must NECESSARILY be greater than or equal to C3, so
the rest of the formula will NEVER be evaluated. If you get to this
point, your formula returns FALSE. Similar things going on in your
second formula. The only thing clear is that you want to return "" if
C3 is "". You seem to want to return "" also when C3 > 0. If so, start
off with

=IF(OR(C3="",C3>0),"",

Then it becomes murky. It looks like you want to return different
things when D3 < C3 and when D3 > C3. This omits what to do if D3 =
C3. You should either add equality to one or the other (so D3 <= C3 or
D3 >= C3) or add D3 = C3 as a separate condition to check.

You're also checking whether B2, B4 or B5 are "". It looks like you'd
usually be returning B3 except in particular circumstances in which
you'd return B2 or B4. Try explaining when IN PROSE when you'd want B2
and when you'd want B4. Then is should be possible to rewrite both
formulas into one similar to

=IF(OR(C3="",C3>0), "", IF(condition for B2, B2, IF(condition for
B4, B4, B3)))

This could actually be simplified to

=IF(C3<=0, IF(condition for B2, B2, IF(condition for B4, B4, B3)),
"")

 

[JimS]

DETROIT LIONS 47 45.5 52% off 19%
MINNESOTA VIKINGS -13 -13.5 48% 81% -0.5
DETROIT LIONS 13.5

CHICAGO BEARS -1 -3 95% off 76% -2 ATLANTA
3.0
ATLANTA FALCONS 42.5 43.5 5% 24%

MIAMI DOLPHINS 46 44.5 49% off 33% FALSE
HOUSTON TEXANS -3 -3.0 51% 67% 0

Can you explain in words what type of problem you're trying to solve?

Probably not, but I'll try. ;--) I download this NFL data from a web
site, and put it into my spread sheet. The two formulas I wrote work
fine, but I have to separate them into two columns. I want to see if
I can combine the two formulas and put the answers into one column.

If the pointspread favorite is the bottom team, (Minnesota, -13,
-13.5) I have one formula and if the favorite (Chicago Bears, -1,-3)
is the top team I have another formula. I couldn't figure out how to
combine the two. The first number is the opening line, and the second
number is the current line.

So, in the first game (Detroit and Minnesota) the spreadsheet finds
the difference in the pointspread lines (-13 and -13.5) (-.5) and then
gives me the name of the team that is favored by the line move, in
this case Detroit, because since the line opened at -13 they are now
getting 13.5.

In the bottom game it spits out Atlanta because Atlanta's line went
from +1 to +3.

In my spreadsheet there is an empty row after and before each game.

If there is no line change (Miami, Houston) it comes back with a
false.

The other stuff in there is over/unders, money lines, etc. They are
not necessary to my computations.