Good Morning. In the attached sheet you will see in Column D there are groups of 3 utilisation figures. There are also the occasional group of 2. I need a piece of code that analyses the blocks of data and returns a average in Column E. The current averages have been manually calculated using simple formula but I need some code as this spreadsheet covers many sites all needing to display similar information.
Thanks in advance for your assistance.
Hi Macca_GB,
In
D2
=IF(C2<>"",(B2-C2)/B2,"")
fill down
In
E2
=IF(D3="",SUM(INDEX(D:D,MAX(IF($D$1:D2=0,ROW($D$1:D2)))+1):D2)/COUNT(INDEX(D:D,MAX(IF($D$1:D2=0,ROW($D$1:D2)))+1):D2),"")
with Crtl + Shift + enter
fill down
Hi Pike, I couldnt get that to work. Could I ask you to insert the formulae in the worksheet so I can see exactly where it goes.
Thanks
Hi Macca_GB,
You had the auto calculation turmed off
Macca_GB
Leave it with me I recheck to night
Hey Macca_GB
this works with a helper column
=IF(AND(D5="",D4=""),SUMPRODUCT(VALUE(0&D$2:D201)*(F$2:F201=F3))/COUNTIF(F$2:F201,F3),"")
If i can work it out without a helper column I post it
What is a helper column?
hi Macca_GB,
Its a when you cant achieve a One cell formula and need and extra column formula to help with the calculation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks