# Thread: Formula Confusion (Excel 2002)

1. ## Formula Confusion (Excel 2002)

Hello,

Can any one tell me how this formula works and how the numbers relate - =MAX(0,\$C\$92*(G43-G\$105)/\$C\$94) where C92 = 419, G43 = 134.3, C94=261

and this one as well please!

=(\$h\$28=1)*F112+(1-\$H\$28)*F106 Where H28 = No, F112=95.8

and maybe even this little one
=IF(G82<YEAR(\$F\$93),\$C\$93,IF(YEAR(\$F\$93)=G82,YEARF RAC(DATE(G82,1,1),\$F\$93)*\$C\$93+(1-YEARFRAC(DATE(G82,1,1),\$F\$93))*\$D\$93,\$D\$93))
where G82=2008, F93=01/01/2009, C93 = 9, D93 = 15

Thanks!

2. ## Re: Formula Confusion (Excel 2002)

Could you attach a small sample workbook that shows these formulas in their context?

3. ## Re: Formula Confusion (Excel 2002)

Hello Hans,

Unfortunatel I cant, I guess I can figure out on my own how they relate. Could you just tell me how the formulas work?

4. ## Re: Formula Confusion (Excel 2002)

The formula

=MAX(0,\$C\$92*(G43-G\$105)/\$C\$94)

calculates \$C\$92*(G43-G\$105)/\$C\$94 and returns that value, unless the result is negative, then it returns 0.

The formula

=(\$h\$28=1)*F112+(1-\$H\$28)*F106

with H28 = "No" makes no sense to me, you can't subtract a text string from a number.

The formula

=IF(G82<YEAR(\$F\$93),\$C\$93,IF(YEAR(\$F\$93)=G82,YEARF RAC(DATE(G82,1,1),\$F\$93)*\$C\$93+(1-YEARFRAC(DATE(G82,1,1),\$F\$93))*\$D\$93,\$D\$93))

returns the value of C93 if the year of the date in F93 is greater than G82. In your example this is the case: 2008 is less than the year of 01/01/2009, so 9 is returned.
If the year of the date in F93 is equal to G82, the formula interpolates between the values of C93 and D93 according to the part of the year that has passed.
If the year of the date in F93 is less than G82, the formula returns the value of D93.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•