I am trying to track the change from Jan to Dec.
I need to pull the Jan data into a single entry and the last data for the year into another cell.
But there is not always a Jan entry, which means the Jan cell is blank.
The correct entry for Jan 2023 is blank and the correct entry for Jan 2024 is 60.18.
The correct entry for 2023-year end is 60.891 and for 2024 it is 15.30.
The data for Jan and end of year can be in either column f or g.
Thank you for your time and consideration.
Richard
Use those formulas
Formula:
Please Login or Register to view this content.
I have, hopefully, clarified the data for the sheet.
I need the first entry for January of 2023 and January of 2024 or whatever year is being tracked.
I have a number of years in the actual spreadsheet and some years have a Jan entry and others do not.
The January value, if there is one goes in the M column. If there is no entry for a given year the M column
is "NA" or blank for that year.
Every year has a last entry for that year, which goes into the N column.
I need to track the change from Jan to the last month with an entry.
Could there be more than two columns to have to look at here? Could there ever be entries in BOTH columns on the SAME row, and if so, which would count?
Last edited by AliGW; 03-19-2024 at 03:15 AM.
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.
For users Excel 365:
If there is only 1 entry on the same row:
Please empty all expected results and try for the whole table (including the header and the years) in one go this single cell formula:I saw too late that you are using Excel 2021. I will soon come up with a solution for Excel 2021.
Please Login or Register to view this content.
Last edited by HansDouwe; 03-19-2024 at 04:21 AM.
Here is a solution for users Excel 2021
Please empty all expected results and try for the different years (in L15):Formula:Please Login or Register to view this content.
And for the rest of the table: Please try (in M15) and copy down for every year:Formula:Please Login or Register to view this content.
First year value.
Formula:Please Login or Register to view this content.
Last year value
Formula:Please Login or Register to view this content.
I have added an actual sheet I am using.
As you can see, column F and G do not always increase. They can go down.
Also, when there is only a Jan entry, it is both the column R and S value.
When a new month for the year has a value, it becomes the column S value.
If you respect the boundaries of the table the formula will be fine.
Formula:Please Login or Register to view this content.
DJunqueira:
The formulas work great, until.
I have two possible entries for Jan 23 in column B, first is a "d" and the second is a "p".
I get a #spill error.
How do I eliminate the #spill error and get the "d" entry for column F or G?
I have 21 data sets and a few have multiple entries for the same month.
Thank you for your time and consideration.
Last edited by rafcaf; 03-23-2024 at 12:26 PM.
Lets see if it solve the question.
Formula:Please Login or Register to view this content.
Tks for the feedback, glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks