Number of Months to Reach Target
Hi All,
From Column A to Column J, these are my monthly sales figures.
From Column L to Column U is the cumulative sales figures.
I would want to write a formula to calculate cell V4 (highlighted in blue), which shows how many months it takes to exceed the target of 28,000 (cell P1).
I have previously done this through a combination of index with the cumulative figure but can't seem to solve it this time.
Many thanks for your help
Last edited by swong1709; 06-12-2017 at 07:00 PM.
Try
=MIN(IF(L4:U4>=P1,L3:U3)) Ctrl Shift Enter
Great Thanks.
Would there be a solution without using arrays?
Try this non-CSE...
=INDEX($L$3:$U$3,MATCH(P1,$L$4:$U$4,1)+(LOOKUP(P1,$L$4:$U$4) < P1))
Last edited by jeffreybrown; 06-12-2017 at 11:41 AM.
HTH
Regards, Jeff
What if P1 was < 12,000?Originally Posted by jeffreybrown
Last edited by 63falcondude; 06-12-2017 at 11:46 AM.
Try this
Formula:
Please Login or Register to view this content.
Or
Formula:Please Login or Register to view this content.
Last edited by AlKey; 06-12-2017 at 12:07 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks