Forum Discussion

Iron Contributor

Feb 22, 2021

Question related to year

Hello Everyone, I am just wondering how to use dcount() function or any function that could result to find the count of the year as shown below in the picture. Please help...???

excel

Formulas and Functions

Excel

Iron Contributor

Feb 22, 2021

Riny_van_Eekelen

Thank you for giving the solution😊

Can you please explain this formula -

{=MATCH(TRUE,Cumulative>=12,0)}

Please?

Riny_van_Eekelen

Platinum Contributor

Feb 22, 2021

Excel

From the inside out, "Cumulative>=F12" creates an array of <<FALSE, FASLE, TRUE, TRUE ....etc>> for every cell in the named range Cumulative. MATCH is finding the position of the first instance of TRUE in that array.

{=MATCH(TRUE,Cumulative>=F12,0)}

So, in this particular example, it finds that the third element in the array is greater than or equal to 36000. Hence, during the third year, the indicated amount has been reached or exceeded.

The curly brackets around the formula indicate that you are an older version of Excel. You can't type them when you write the formula, but you need to finish the formula by pressing Ctrl-Shift-Enter simultaneously. Then, the curly brackets will appear. On newer Excel versions this is not necessary anymore.

SergeiBaklan
Diamond Contributor
Feb 22, 2021
Riny_van_Eekelen

For pre-DA Excel

=LOOKUP(F12,Cumulative,Years)

shall work without CSE
- Riny_van_Eekelen
  Platinum Contributor
  Feb 22, 2021
  SergeiBaklan True, but not in case the yearly amounts are not evenly distributed.
  - SergeiBaklan
    Diamond Contributor
    Feb 22, 2021
    Riny_van_Eekelen , yes, it works from left to right. When to avoid CSE
    
    =MATCH(1,INDEX(--(Cumulative>=F12),0),0)
Excel
Iron Contributor
Feb 22, 2021
Riny_van_Eekelen
You are awesome. Thank you so much sir😊😊