Formula to get the desired average at the end of a week

[Vj Raj]

hey guys,

I have attached a sample file. In the file, in Week 3 (highlighted), the weekly average is 2051.5. This is based on the values for Mon and Tue in the table. The expected weekly average should be 2000. Based on this criteria, how do I write a formula in cells B6, D6, F6, H6, J6, L6 and N6, that tells me, to achieve the 2000 weekly average value, the values for wed, thu, fri, sat and sun should be this much. The values in bold are the daily targets and weekly average targets.

I hope my explanation above makes sense. If not, please feel free to ask your questions so that we can find a solution together.

Appreciate any help.

thanks

VJ

[6StringJazzer]

I don't think you can do that, because any such formula must refer to all cells for each day, therefore referring to itself, and that will create circular references. I tried.

However, you can have a single formula that will give you the average number you must reach in all blank days:

Please Login or Register  to view this content.

See suggestion in attached.

[bsalv]

i'm with a circulair problem when using that value in columns B,D,F,H,J,L,...

Please Login or Register  to view this content.

Edit : almost the same formula as previous, 1st part for count(...)=0 is unnecessary

[Vj Raj]

Hi Jeff,

Thank you so much. The formula worked like a charm. I was getting the circular ref error earlier. Now I can use the value in the added column and reference that to the weekdays using maybe a WEEKDAY formula. I might have to make some changes to the table to make it work. But, my issue is solved for now.

VJ

[Vj Raj]

Hey Bsalv,

You gave me a solution for my next step of getting the value in the cells for each weekday. I really appreciate the help.

VJ

[6StringJazzer]

You're welcome, and thanks for the rep! I love Toronto, beautiful city. I haven't been back there since 1980.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

[bsalv]

You gave me a solution for my next step of getting the value in the cells for each weekday.

Yes, but with a small circular reference problem ?

[Vj Raj]

Yes, I am working on fixing it now. If I find a solution for that, I will update the post with the solution.

Thank you

VJ

[Vj Raj]

Yes, but with a small circular reference problem ?

It seems like the circular ref problem is occurring because of the empty cells for future weeks. I tried adding in random values for future weeks and it solved the problem. So as I input values for future weeks the issue will correct itself.

This is because the formula we've used is

Please Login or Register  to view this content.

and there is no value in R8 because it is the weekly average for Week 5 and we are currently in Week 3. The same happens for other future weeks too. But, entering values for future weeks seem to solve this problem

VJ