Need Help Understanding These Two Formulas

Hello All,


I am pretty new to excel and needed to understand the following two formulas. Can anyone simplify this for me in English term? This is calculating a Numerator and a denominator of Sales Volume of people with credit scores between 350 and 599.

1) Numerator: WAF_LT600_num: Sum(IIf([fico] Between 350 And 599,IIf([sales_volume]<0,0,[SALES_VOLUME]*[FICO]),0))

2) Denominator: WAF_LT600_denom: Sum(IIf([fico] Between 350 And 599,IIf([SALES_VOLUME]<0,0,[SALES_VOLUME]),0))

I noticed that they are almost the same, except the first one includes a [Sales Volume]*[FICO] and the second one has [Sales Volume] by itself.

Thank you very much for your help!

Hendrix -

Those dont look like excel formulas, where did they come from?

Those appear to be Access formulas (for use in a query).

The first portion of the formula is checking to ensure Fico is between 350 and 599 and the second portion checks if the sales_volume is not negative. If these checks work, then the calculation is made, if not then it gives a 0.

Where are you getting this from?

In English the first one simply says If FICO is between 350 and 599, then go to the next test to figure out the result; if it is not between those numbers, the result is 0. The next test is if sales volume is negative use 0 as the result, otherwise use sales volume x FICO.

As you note the second one is the same except it does not multiply by FICO.

This whole thing makes no sense to me because if FICO is between 350 and 599, and sales volume >= 0, then the numerator divided by the denominator (presumably you are calculating these to be able to do that division) is

(Sales Volume) x (FICO) / (Sales Volume)

Which is simply FICO. I don't know why a division is necessary. (If the conditions aren't met then the result will be 0, or possibly a divide-by-zero error.)

Perhaps you missed my post #2?

Apparently, Numerator is taking SALES_VOLUME times FICO, while Denominator is not

Like I said - where do they come from?

Ford has asked you three times now, are these Excel formulas or are they Access formulas?
To clearify: what software do these formulas of yours appear in?

henrock24, please take the time to review our rules. There aren't many, and they are all important.

You have asked this question multiple times in different forums and I have closed some duplicates. In this case I moved the thread from Excel to Access, and then merged it with a duplicate that had a response in it (post #6 above).

Please do not submit any more duplicate threads. It's against the rules.