0

I would like to do an average between 7 days, the problem is that the information I want the function to look for is 4 rows under one another. Maybe it has something to do with the function rows, or counta ?

The second thing is, that in column B I have dates, on which I had a question yesterday and somebody solved it, so thank you. So now I have one visible date and then 3 same but invisible dates (based on formatting), and I want to look for data in another column (C) which gives me the results from column (D) and makes an average with data from other dates.

So far i was able to come up with this =AVERAGE(AND(LOOKUP(TODAY();B12:B;D12:D);(LOOKUP("kcal";C12:C;D12:D)));(AND(LOOKUP(TODAY()-1;B12:B;D12:D);(LOOKUP("kcal";C12:C;D12:D))))) and then it will continue TODAY()-2,-3....-7.

But this gives me "Can't devide by zero" error

Without the invisible dates it would be easy to do, but then I wouldnt be able to sort it. It would look like this =AVERAGE(LOOKUP(TODAY();B12:B;D12:D);(LOOKUP(TODAY()-1;B12:B;D12:D)))

Link to my fake spreadsheet: https://docs.google.com/spreadsheets/d/1tW8Wi18Ka12l4DNkRYoZdazu5X2MitKqwp38AGVzoVI

Edit: Only possible solution I came up with now was to put in column A values from column D, but only the first value and 4x, now this formula: =AVERAGE(LOOKUP(TODAY();B12:B;A12:A);(LOOKUP(TODAY()-1;B12:B;A12:A));(LOOKUP(TODAY()-2;B12:B;A12:A)); ... till -7 works. Then I can hide with formatting the column A, its not a perfect solution and Im sure there is a better one.

1 Answer 1

0

See my newly added sheet ("Erik Help"). This formula is in cell D1:

=AVERAGE(FILTER(D12:D;B12:B>=TODAY()-6;B12:B<=TODAY();C12:C="calories";D12:D<>""))

I used FILTER to filter in only those values that meet these conditions:

• Has a date between today and six days ago • Contains the word "calories" in Col C • Contains a number in Col D

Then AVERAGE acts only on those values.

By the way, your own formula was producing the wrong result, because you were including eight days, not seven.

You will notice, in my sheet, that there is no longer any need for the Col-A values.

I also replaced your many row-by-row formulas in D11:D with one formula in D11 that produces the header and all results for rows where there are food values. You didn't specifically ask for this; but since I noticed it while I was in there, I gave you that option as well.

3
  • oh wow, that's a very smart way of tackling it ! I guess I would have figured out after 7 days that it is still showing me N/A lol, should have tested that. I did not know that it was possible to do the thing in cell D11 ! Last question that sparked in my head thanks to the clever solution in cell D11, is there a solution to the situation in column B? Feb 3, 2022 at 22:39
  • I don't know what "the situation in column B" is. At a glance, it appears you are wanting to sort/re-sort by date in Col B. And if that is the case, you must have a date in each cell. If you are asking something different, however, per Stack's intention of keeping things organized for future reference, please post each question separately. I will keep an eye out for it if you do post the new question.
    – Erik Tyler
    Feb 3, 2022 at 22:53
  • I posted the question yesterday. I wanted to filter out data with the "Slicer" function - to show me just todays data, because when the list gets longer, for example in 3 months, i would have to do a lot of scrolling. But now that you said I must have date in each cell, then there is no workaround. Anyways, thanks so much for the help. Feb 3, 2022 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.