Hello -
I want excel to perform following by formula.
I have set of numbers in one column. There are some dates where the count is 0 so excel to find out the next positive number and take an average of the cells from 0 until next positive number and provide it in next column
Date Planned Desired Plan
1-Aug-19 0 40
2-Aug-19 80 40
5-Aug-19 0 30
6-Aug-19 0 30
7-Aug-19 90 30
8-Aug-19 0 20
9-Aug-19 0 20
12-Aug-19 60 20
13-Aug-19 0 25
14-Aug-19 50 25
Last edited by sandiptemkar; 09-09-2019 at 07:58 AM.
Try this in C2:
=IF(AND(B2=0,B1<>0),INDEX(B2:$B$11,MATCH(TRUE,INDEX(B2:$B$11<>0,0),0))/MATCH(TRUE,INDEX(B2:$B$11<>0,0),0),C1)
OMG --- What a formula --- it worked nicely --- thanks much --- outstanding -- my query got resolved. Added reputation.
Last edited by sandiptemkar; 09-09-2019 at 08:20 AM.
See the attached file.
Yes Phuocam - I have updated my reply -- It worked --- I had considered some other columns -- thanks -- query has resolved.
You are welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks