Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Confusion (Excel 2002)

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

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •