This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 55.00000000000001%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESB%3C/text%3E%3C/svg%3E)
Dearest Wizards,
Col A has assorted dates
Col B has various text titles; one of the many titles is the word "DIVIDEND"
Col C has various dollar amounts.
What formula can be put in D1 that will give me the AVERAGE monthly dividends?
TIA for your help,
Sam
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
I tried this with the AI response and it provided an answer based on its assumption that the data range was rows 1-100. The range is actually generated with a FILTER function so is dynamic. Again, thanks for your help,
Sam
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 3%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
Hi,
Use this formula in cell E2:
=GROUPBY(EOMONTH(+A2:A6, 0), C2:C6, AVERAGE,, 0,, B2:B6 = "Dividend")
Format data in column E as date so in Format Cells choose Custom and in the Type box put this format: mmm-yy
Hope this helps.
IlirU
Hi,
In cell E2, enter this formula
=DROP(GROUPBY(HSTACK(MONTH(A2:A6),TEXT(A2:A6,"mmm-yy")),C2:C6,AVERAGE,,0,,B2:B6="dividend"),,1)
Hope this helps.