How to find date when I reach 1.000 in YTD sales

[Bjorn]

Dear all,

I have a file where I have dates in column A (Jan 1,2 etc), daily sales in column B and YTD sales in column C.

I would like to find the date when my YTD sales rise above 1.000 (or any number).

If I knew that the YTD sales will be exactly 1.000 I could use index() + match(). But this is unlikely to be the case.

In the example below, I would like the formula to return Feb 19.

Date daily ytd
18/fev 22 991
19/fev 11 1002
20/fev 18
1020

Any help would be appreciated.

Regards
Bjorn

 

[Luke M]

=INDEX(A:A,MATCH(1000-1,C:C,0)+1)

The trick is doing the -1/+1. If the number 1000 doesn't exist, MATCH is going to give you the position one less than what you want, so we would need the +1. But in case the 1000 number does exist, we would then be too high. So, we subtract one.

Note that you don't have to write 1000-1 in the formula, you can just write 999. I just wrote it that way for explanation purposes.

 

[Somendra Misra]

Hi Bjorn,

Welcome to the forum.
With dates in column A sales in B in YTD in B use below formula
=LOOKUP(1010,C2:C5,A2:A5)

Change the value 1010 to the value you want to c +1.

Regards!

 

[SirJB7]

Hi, Bjorn!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, try this:
E1: threshold, e.g., 1000
F1 or anywhere else except in columns A:C : =INDICE(A:A;COINCIDIR(E1-0,001;C:C;1)+1) -----> in english: =INDEX(A:A,MATCH(E1-0.001.C:C.1)+1)
The tricky part is the -0.001 to make MATCH work for exact matches when used the 3rd parameter for greater than (1). Adjust it properly if your actual values have such decimal places or more.

Regards!

 

[Bjorn]

Thank you all! It worked like a charm.

Your quick replies exceeded my wildest expectations.

Regards

 

[Luke M]

@SirJB7
5 minutes late to the solution again buddy. I'll let it slide since I know you have to translate your formulas into English.

 

[NARAYANK991]

Hi ,

I do not know whether there are any conditions for the input data , but it might be worth considering the following :

1. Will there be duplicates , both below 1000 , and equal to or above 1000

2. Will the data be in ordered in any way or will either the dates or the amounts be at random ?

Narayan

 

[Somendra Misra]

@NARAYANK991 ...
Hi.
I dont think YTD can have duplicates if we talk about a single product or service or anything. OP can through more light. As the solutions given by all is bsed on the data provided by him.

Somendra.

 

[SirJB7]

5 minutes late to the solution again buddy. I'll let it slide since I know you have to translate your formulas into English.

Hi, buddy!
I love your kindness, and I took as long because I was checking that no new words were added to the Dictionary of the Spanish Royal Academy in this new 2014...
Regards!

 

[bobhc]

Good evening SirJB7 have a carlsberg and read just some of the changes:-


The Royal Spanish Academy — the official arbiter of the Spanish language — recently announced that it will add the word "Espanglish" to the 2014 edition of its dictionary along with "goglear," "tuitear " and "guasapear" These are some words that will have their definitions changed, “femenino” (feminine). Among others, one of the accepted definitions for feminine as published by the RAE is “weak or feeble.” Parallel changes will be made to the definition of “masculino” (masculine) in the dictionary: El Comercio reports that a the definition of “manly and energetic” will be removed.

Another word undergoing alteration is “babosear” (generally translated as “to slobber”), which has as one of its secondary definitions “to court or serve a woman excessively.” Another candidate for change is “gozar” (to enjoy), which currently carries a secondary definition of “to know a woman carnally.

 

[SirJB7]

Hi, b(ut)ob(ut)hc!

Those are absolutely tolerable, but not the ones below.

I'm not a man of the Literature side, so nothing about poetry, prose, or language purism, but what those drunk (for being polite) guys did on June 2012 when they incorporated 1697 new words to the Official Dictionary it's hair-rising as well as indignant and many other adjectives.
http://www.bbc.co.uk/mundo/noticias/2012/06/120622_cultura_palabras_diccionario_espanol_rae_dp.shtml
http://panorama.com.ve/portal/app/push/noticia23410.php
If hard to go thru in Spanish, check for "1697 new words incorporated 2012" or something alike.

Regards!