nested if function maybe?

[dbfreak]

Hey everyone,

I hope everyone is having a good day!

I need a hand.

I'm trying to get a money total on one field "total" based off of 4 different senarios.

the senarios come from a tab with a drop down that has 4 options and i would like the total to calculate only certain fields based off of what was selected in the field with the drop down. example:

fields that will be included in the calculations:

adj gross

net total

overage

bonus

% (this is a field too)

tab down selections:

vs

+

gt

bonus

so what id like my total field to do is calculate different fields based on what was selected in the drop down.

ex and this is the more complicated one.

(below is me talking it out)

if "rate" is equal to vs, and adj gross is greater than gt, then % times adj gross, if adj gross is < gt, then = gt.

looking at this now there are probably a couple more if's that need to happen. So it may be up to six or so if statements.

Which way would be the smarter way to build this calc? case function, if function, or a script?

I have done my internet search for help but i have found a lot of explanations that i cant seem to grasp due them showing day of the week examples.

Does this make sense to anyone and if so can you point me in a direction that will help me understand what i need to do?

thanks all!

ric

[dbfreak]

Ive got this to work but now im trying to make one last adjustment. I need the total never to be less than the guarantee amount. Any suggestions would be greatly appreciated.

Here's what I've got:

If ( DEAL = "guarantee" ; GUARANTEE ; If (DEAL = "plus"; GUARANTEE + OVERAGE ; If (DEAL = "bonus"; GUARANTEE + BONUS ; If ( DEAL = "vs" ; ARTIST PERCENTAGE * ADJ GROSS; " " ))))

this seems to work fine. i just cant figure out how to finish the last part.

thanks!

[comment]

A Case() statement is more convenient that nested If()'s, IMHO. Try something like:

Max (

Case (

DEAL = "plus" ; GUARANTEE + OVERAGE ;

DEAL = "bonus" ; GUARANTEE + BONUS ;

DEAL = "vs" ; ARTIST PERCENTAGE * ADJ GROSS

) ;

GUARANTEE

)

[dbfreak]

amazing!

thank you a million times!

what is the Max(

doing? I read a little about Case statements but didn't see that part.

[comment]

The Max() part makes sure the result is never less than GUARANTEE. It has nothing to with Case(). The logic goes:

a = Case (...)

b = GUARANTEE

result = Max ( a ; b )