Returning a date when the sum of the values reaches a certain value
Hello Everyone,
I am new to this section and this first post. I require your help in getting a solution for my below scenario.
What i want is a formula that returns me the date value. The date value should be such that the sum of of the values below should add 12.
For eg
01May 02May 03May 04May 05May 06May 07May
2 4 2 2 2 4 4
The value i require is "05May" as the sum of value from left to right should add upto 12.
Please assist and let me know in case you need further information.
Last edited by V08IPS; 05-16-2023 at 05:10 AM.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
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.
Hi,V08IPS,
Assuming that your sample data starts from A1 and the corresponding values from A2 than, in A3 insert a formula
Formula:
Please Login or Register to view this content.
and drag to the right
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
Thank you. This is well noted.Originally Posted by AliGW
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
Thank you. This helps. But i have a lot of rows and columns and i might have to creat a different sheet to get the increasing values using your formula.Hi,V08IPS,
Assuming that your sample data starts from A1 and the corresponding values from A2 than, in A3 insert a formula
Formula:Please Login or Register to view this content.
and drag to the right
What i am looking for is something like this in the attachment.
Please assist.
Try this in cell E9 and copy down.I do not come up with the anticipated 3-May-23 you show in the second row. I get 2-May-23 with this formula. What am I missing?Formula:Please Login or Register to view this content.
Dave
Although this will return what your sample expects. In E9 and copied down.Formula:Please Login or Register to view this content.
Thank you FlameRetired. This is very close to what i want.
I have also found another solution. Hope it will help other users aswell.
"Cltr +Shift +Enter" for Array
=INDEX($C$1:$L$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($C2,0,0,1,COLUMN($C2:$L2)-COLUMN($C2)+1))>=12,0))
Good deal. Thank you for the feedback and marking your thread Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks