Applying an average to remaining blank space in a series

pdeemartin58

New Member
Joined
Feb 4, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Assist in formulas C3 and D3 greatly appreciated.

C3 solution should give the value of A3 - the totals filled into the series e3:p3 + the total for the remaining blank cells if they each contained the current average of the series.
D3 similar idea but the blank cells in the series would each be filled in with the last amount entered (May in Exhibit A below).

Each upcoming month a new entry is inserted, and the formulas should reflect the new data without requiring a rewrite of the formula (I hope!).

Thank you in advance.

PS: Sorry, in hindsight I should have left Column B off my exhibit as it has no bearing on the questions.


Exhibit-A.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is this what you mean? If not, could you work out what the results should be manually and tell what they are and how you got them.

25 02 05.xlsm
ABCDEFGHIJKLMNOP
2JanFebMarAprMayJunJulAugSepOctNovDec
311,290,858794,597453,378760,898883,774842,356962,980923,434
Sheet2 (2)
Cell Formulas
RangeFormula
C3C3=A3-SUM(E3:P3,AVERAGE(E3:P3)*COUNTBLANK(E3:P3))
D3D3=A3-SUM(E3:P3,LOOKUP(9^9,E3:P3)*COUNTBLANK(E3:P3))


That emoji should be a "P". No idea how that got there.
The keyboard shortcut for that emoji is :p so that's how it got there. (I tricked the shortcut here) :)
Use upper case for the cell reference and that particular one won't bother you (but there may be others :cool: )
Lower case e3:p3
Upper case E3:P3
 
Upvote 0
Solution
That works perfect! Thanks for all the info and I'll look into the XL2BB feature more. I did install the add-in but I don't really know how to affect its' use yet.
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top