# Multiple IF Tests

This topic is 2927 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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.

• 1
##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

• 1
##### Share on other sites

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

##### Share on other sites

This topic is 2927 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account

• ### Similar Content

• #### Checkbox sets and looping IF statements

By dougroth,

• 6 replies
• 1,939 views
• #### Nested IF's and CASE's

By Kurt Hansen,

• 8 replies
• 945 views
• #### Baffled by calculation error

By kathrynfw,

• 4 replies
• 1,085 views
• #### Conditional Summing

By Gurratheboy,

• 998 views
• #### IF Script skipping steps

By eddiedredge,

• 21 replies
• 1,269 views
×
×
• Create New...