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

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.

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

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

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].

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