Results 1 to 4 of 4

IF Then - using 12 months - ongoing average

Threaded View

  1. #1
    Registered User
    Join Date
    03-07-2009
    Location
    Birmingham, Al USA
    MS-Off Ver
    Excel 2003
    Posts
    44

    IF Then - using 12 months - ongoing average

    This is pretty basic but it’s the best that I could do not knowing VBA or an easier way. I hope someone can help in making it better. The formula works but under very strict conditions. The spreadsheet has columns for all 12 months, a yearly total and an average column. The “If – Then” is for the average column for entire year. No matter what the month it will calculate the average using the appropriate number of months to divide by, now here’s the catch; each previous month has to have a number greater than “0” and luckily I have but if a previous month has a "0" it blows the formula out of the water.
    I had to split the into IF-Then into two sections because of the embedding limitations so when I have a lot of items that I need to do this for you can see I am having to reproduce this formula multiple times and tracking gets a little confusing.
    Jan – Column C Feb – Column D Mar – Column E Apr – Column F May – Column G
    Jun – Column H Jul – Column I Aug – Column J C Sept – Column K Oct – Column L
    Nov – Column M Dec – Column N

    Jan thru June: (Cell V4)
    =IF((AND($C$19>0,$D$19<=0)),$C$19,IF((AND($D$19>0,$E$19<=0)),(($C$19+$D$19)/2),IF((AND($E$19>0,$F$19<=0)),(($C$19+$D$19+$E$19)/3),IF((AND($F$19>0,$G$19<=0)),(($C$19+$D$19+$E$19+$F$19)/4),IF((AND($G$19>0,$H$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19)/5),IF((AND($H$19>0,$I$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19)/6),0))))))

    July thru Dec: (Cell V5)
    =IF((AND($I$19>0,$J$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19)/7),IF((AND($J$19>0,$K$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19)/8),IF((AND($K$19>0,$L$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19)/9),IF((AND($L$19>0,$M$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19)/10),IF((AND($M$19>0,$N$19<=0)),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19+$M$19)/11),IF(($N$19>0),(($C$19+$D$19+$E$19+$F$19+$G$19+$H$19+$I$19+$J$19+$K$19+$L$19+$M$19+$N$19)/12),0))))))

    Average Column: (Cell I19 is the cell for July Average for this particular Item)
    =IF(I19>0,V5,V4)
    Attached Files Attached Files
    Last edited by harleypop; 03-08-2009 at 01:45 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1