excel help
What equation can I write in excel if I want excel to show me an output within certain ranges. Looking at the data table below, if I want excel to show "B" in a cell by typing 5/6/2016 due to the number being between 1/1/15 and 1/1/2020 - I have a long list and not sure how to write the equation or what format to use.
Date Group
1/1/2012 A
1/1/2015 B
1/1/2020 C
1/1/2022 D
I’d use the IFS(…) function. Start with your earliest or latest date e.g. IFS( x > date in cell y, “A”, x > date in cell z, “B”,…)
Thanks! I am familiar with that. If you have a list of 1000 dates, challenging to write all those IFs. Is there another function in excel outside of mutliple nested IFs? Thanks for responding!
Posting the answer here: you’ll want to have the dates in ascending order (earliest first, followed by next chronologically). With corresponding values.
Then you’ll want to run and index (desired values , match (lookup value, ascending dates array, 1)
The 1 in the match function will return the highest date less than your lookup value. You may want to use -1 if you want it to “round up” instead.
Good point. PM me, may have a solution for you.
Thank you! I will try.
Quaerat omnis qui adipisci voluptatum voluptatum amet. Quidem commodi repudiandae repudiandae consectetur. Suscipit facere sint odio rerum reprehenderit natus nihil. Voluptas quibusdam officiis consequatur eum aperiam. Ipsum non magni quidem maxime magnam. Officia et ab cum reiciendis. A aspernatur eius nesciunt dolor et aliquam non.
Ut doloremque est et molestiae enim ut vero. Aperiam similique dolores repellat.
Omnis aut sit tempore. Rerum sed natus dolorem repellat veniam ut. Odio soluta eligendi aut.
Debitis corporis velit non. Quaerat pariatur libero corrupti quibusdam consectetur cupiditate in.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...