Attached is a small section of a excel spreadsheet data base that I can not seem to get the formulas to work at all or not provide the correct answers and I've tried everything I can think of
and that I've used elsewhere before all just didn't work.
There is a text box box on the sheet that explains what I'm trying to accomplish but I'm at a dead end till the issues can be resolved.
OK - for starters, where the text 'E5' is showing, you need to delete the cell contents, set the formatting to GENERAL, and then enter the cell reference =E5 again.
This for E8:
=SUM(FILTER($M$3:$M$66,(YEAR($L$3:$L$66)=YEAR($E$6))*($N$3:$N$66=$E$5)))
E14:
=SUM(FILTER($M$3:$M$66,(YEAR($L$3:$L$66)=YEAR($E$6))*(MONTH($L$3:$L$66)=MONTH(DATEVALUE($E$7&" 1"))*($N$3:$N$66=$E$5))))
There is no formula in E20, so that's what's wrong there. What VALUE are you expecting? Do you want the average of monthly averages, or just an overall average?
Please explain the difference between the calculations required in column B and those in E.
You should ALWAYS include manually calculated expected results in sample workbooks!
Work in progress attached.
Last edited by AliGW; 06-15-2024 at 01:39 AM. Reason: Workbook added.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
E8=sum(filter($m$3:$m$66,(year($l$3:$l$66)=year($e$6))*(month($l$3:$l$66)=month(e7&0))*(n3:n66=e5)))
Not E8 - that's the year value. You mean E14.
Good morning ALiGW,
Thank you for looking at these issue's for me ! The difference between the two columns is to physically see or track the difference in the general cost of things from the current cost of
living in the current year against the rise or fall of living cost the previous year. As for the two bottom empty formula cells, frankly I haven't figured out how to write a formula yet, they are or will be to track the AVERAGE cost by month based on the number of months into the year. Don't really have an idea on where to start to build the formula so it base's the resulting
AVERAGE on the amounts and the number of months already past in the current year, for example not simply averaging on 12 months rather 6 months as we are already in June which would be the currently the number of months.
Fred
There's no previous year data in the sample sheet - please add some and I'll have another look.frankly I haven't figured out how to write a formula yet,
So you want to average each month and then average those values?they are or will be to track the AVERAGE cost by month based on the number of months into the year.
This bit I understand, but it's more about how you want to average the months themselves.AVERAGE on the amounts and the number of months already past in the current year,
Deleted by JT
Last edited by JohnTopley; 06-15-2024 at 07:48 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
John - that was addressed in post #2.
In E20
Formula:
Please Login or Register to view this content.
in B20
Formula:Please Login or Register to view this content.
Add "IFERROR(formula,"") to all the formula.
See attached
It would be better to put your "Database" in a separate sheet with ALL required years: separate data "collection" from your "reporting".
And another sheet with all your "parameters" e.g. columns H:J on your current post
Last edited by JohnTopley; 06-15-2024 at 08:29 AM.
Good evening Ali,
Sorry it took so long to get back to you regarding your question about the Averaging I was too busy to work on the database till now. Anyway regarding the Average problem in the bottom cell I was able to figure it out for a change on my own. However If you could assist me on one more thing it would be a big help.
> Column "C" provides two choices i.e. Income or Expense >DATA Validation formula (=$AC$7:$AC$10)<
> Column "D" provides choices based on column "C" >DATA Validation formula (=INDIRECT $C7&"_TYPE")<
> Column "E" provides choices based on column "D" >DATA Validation formula (=INDIRECT ($D7)
Is there a way to add another selection column to the column "D" DATA Validation? I need to be able to access a column named "Transfers" I tried this but it didn't work:
(=INDIRECT $C7&"_TYPE"&_"Transfers") Without this ability column "E" wont allow me to choose where a transfer in to INCOME came from.
None of this appears to be in the original workbook, so I am completely lost about what you are referring to.
I reminded you before about the need for sample workbooks with expected results and clear annotations.
As you have started a new thread for the new query, please sign this thread off.
Glad to have helped.
Please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks