Limitation to functions.

  • Thread starter Thread starter Steve Cohen
  • Start date Start date
S

Steve Cohen

I believe I read in the help file for Excel that up to seven IF functions
can be nested. Is there any way around this?
I have a formula that is the following....

=IF(F4="Winings",(A3+B3),IF(F4="Deposit",ABS(A4),IF(F4="Withdrawal",-ABS(A4)
,IF(F4="Bet",-ABS(A4),IF(F4="Bonus",ABS(A4),IF(F4="Void",+ABS(A3),IF(F4="Fee
",-ABS(A4),"")))))))

I need to add at least one more option "Lose"

=IF(F4="Winings",(A3+B3),IF(F4="Deposit",ABS(A4),IF(F4="Withdrawal",-ABS(A4)
,IF(F4="Bet",-ABS(A4),IF(F4="Bonus",ABS(A4),IF(F4="Void",+ABS(A3),IF(F4="Fee
",-ABS(A4),IF(F4="Lose",-ABS(A4),""))))))))

Dong it the way I have a above doesn't work, Is there another way to
acomplish this?

Thanks

Steve
 
Hi Steve:

(Untested):

=IF(F4="Winings",(A3+B3),IF(OR(F4="Deposit",F4="Bonus"),ABS(A4),IF(OR(F4="Wi
thdrawal",F4="Lose",F4="Bet",F4="Fee"),-ABS(A4),IF(F4="Void",+ABS(A3),""))))

Regards,

Vasant.
 
Vasant Nanavati said:
Hi Steve:

(Untested):

=IF(F4="Winings",(A3+B3),IF(OR(F4="Deposit",F4="Bonus"),ABS(A4),IF(OR(F4
="Wi
Click to expand...
thdrawal",F4="Lose",F4="Bet",F4="Fee"),-ABS(A4),IF(F4="Void",+ABS(A3),""
))))

The alternate way is, of course, to use two cells, one of which has an
intermediate set of calculations.


--
--
Fabian
Humans have to stop treating each other like they treat us ants. Think
about it. If we build, say, a pair of very tall structures, like two
anthills side-by-side, some stupid human swoops in out of nowhere and
knocks them down. Or humans will drop food on the ground near us. we
think it's for us, but those same humans will also try to kill us! I
have no sense of irony, I'm just an ant. But if I did, I'm sure I'd
notice that.
 
I was going to suggest this as well. What you could do is have your last IF
statement point to another cell (instead of the "" you have there now) that
has yet another series of IF statements. This is how you can get around that
limit of nested IFs.

MRO
 
Gromit,

Your images aren't making it to the newsservers at MS (which is a good thing).

These groups are mostly plain text only. You might find it easier and others
may find it more helpful if you just posted in plain text.
 
Oh, Sorry about that.

Not sure if this thread is dead or not.

Still think VLOOKUPs are the easiest and most elegant solution
though...

G

If anyone's interested I'll elaborate.
 
I'm trying to figure out a way to get today's date entereind into Cell
D(whatever) when F (whatever) is set to Bet.

I don't want to use the TODAY() funcution because I don't what the date in
that cell to change when the book is closed then opened again.

Thanks

Steve
 
Followup set to microsoft.public.excel.programming.

One way:

Put this in the worksheet code module (right-click on the worksheet
tab, choose View Code, paste the code in the window that opens,
then click the XL icon on the toolbar to return to XL)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target(1)
If Not Intersect(.Cells, Columns("F")) Is Nothing Then _
If .Value = "Bet" Then .Offset(0, -2).Value = Date
End With
End Sub
 
Back
Top