Multiple IF Tests

[puma55]

im trying to get my solution to generate pricing off of three separate fields and I'm stuck.

 

ex.    If ( Package = "top 120" and Number of TVs = "1" and DVR = "no" ; "30" ; "" ,  If ( Package = "top 120" and Number of TVs = "2" and DVR = "no" ; "37" ; "" ))

 

that returns "too many parameters"

 

 

ex.  If ( Package = "top 120" and Number of TVs = "1" and DVR = "no" ; "30" ; "") ,  If ( Package = "top 120" and Number of TVs = "2" and DVR = "no" ; "37" ; "" )

 

that returns "an operator is expected"

 

 

if done properly there would be 24 different "prices" generated from the combination of three fields.  3 different packages, # or tv's 1-4 and with or without a DVR. i know im close but cant seem to make it work, any help would be great

 

thank you

dave

[comment]

I suggest you try the Case() function instead of multiple If())s (which would need to be nested in order for this to work).

 

A better solution would avoid hard-coding data in calculation formula - as prices are known to fluctuate over time.

[bruceR]

Generally this kind of thing should not be a calc at all; it should be in a price table, with a lookup.

[puma55]

both of those replies are a bit beyond my current knowledge...  would someone be willing to walk me thru it step by step?

[comment]

would someone be willing to walk me thru it step by step?

 

The Case() solution would look something like this:

Case  (
Package = "top 120" and Number of TVs = "1" and DVR = "no" ; "30" ;
Package = "top 120" and Number of TVs = "2" and DVR = "no" ; "37" ;
Package = "top 120" and Number of TVs = "3" and DVR = "no" ; "44" ;
...
Package = "package C" and Number of TVs = "3" and DVR = "yes" ; "148";
Package = "package C" and Number of TVs = "4" and DVR = "yes" ; "155"
)

The correct solution would have a Prices table with fields for {Package, NumberofTVs, DVR, Price}. There would be an individual record for each combination (24 records in your example). Then you would use a relationship based on matching all three fields {Package, NumberofTVs, DVR} to lookup the appropriate price from the Prices table into the Price field in ... [the table where you're currently doing the calculation].

[puma55]

got it!  built table with relationships.. works perfectly.. thanks for all the help