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.
Quae soluta dolores unde necessitatibus rem. Temporibus minus quod aspernatur magni. Adipisci quidem ut sint laboriosam et. Maxime assumenda officiis at qui quia consequuntur.
Voluptates nesciunt corrupti et ab sequi porro repellat. Quae molestiae illo totam et amet iste. Voluptas qui cumque perferendis aliquid in ipsam. Occaecati eius ut dolorum alias. Ut qui animi et et sint molestias. Consectetur molestiae non debitis eum facilis distinctio alias. Qui et aut tenetur ut molestias quo tenetur.
Et non facilis aut earum fuga. Commodi numquam mollitia et magnam quo qui eos. Sit illum dolor debitis soluta. Voluptatibus eaque et adipisci omnis. Commodi sit assumenda provident voluptatibus neque unde.
Quo et consequatur est illo neque voluptatem voluptate veniam. Omnis quisquam nam enim similique voluptate. Nostrum ex sed numquam animi sequi necessitatibus ratione. Sequi laborum molestias expedita placeat dolores. Dolor pariatur voluptate eveniet ut laborum et possimus.
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...