•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to automatically assign a number determined by the rank in a series of dates ?
Our take
Struggling to assign ranks based on dates in your spreadsheet? You’re not alone. Many users find it challenging to automatically generate a sequential number in one column corresponding to the chronological order of dates in another. Fortunately, with a combination of Excel functions, you can create this dynamic ranking system that updates as you add new dates.
I'm sure it's trivial for you excel wizards but I haven't found a way to do this yet : I need to generate a number in column A that's a function of the chronological order of the corresponding date in column D, ie the earliest date gets "1" and so on (this spreadsheet is completed on the fly so the column D is manually updated with the date when the customer sends me what I need, so the column A stays blank when there's no matching date, for which I'll just use a IF() function), see pic : https://tmpfiles.org/25810180/screenshhh.png
I googled quite a bit and still haven't found a function that does this.
I use office 360 though not the latest version.
Sorry for the broken English !
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.Hello Reddit pros, I am trying to create/find a formula that will do the following: Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes? I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this: =IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"") This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight. I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed! submitted by /u/ClandestineGhost [link] [comments]
- What formula for sequencing number but when adjacent row is blank it wont get sequencedI have tried this formula form chat gpt: =IF(B14="","",COUNTIF($B$14:B14,"<>")) =IF(B14="","",SUBTOTAL(103,$B$14:B14)) =IF(B14="","",SUBTOTAL(103,B$14:B14)) But the blank row still get counted like: B1 has data, number 1 B2 has no data, blank B3 has data, number 3 What i want is: B1 has data, so its number 1 B2 has no data, so its blank B3 has data, so its number 2 Iam using google spreadsheets, sorry if it confusing iam still new here *edit: thanks for the help everyone i think the previous formula not doing what i wanted is because the cell that i thought blank is filled with vlookup formula, iam using =IF(B14 = "", "", MAX(A$13:A13) + 1) and it work! submitted by /u/Barusan_buat [link] [comments]
- Fill out a ranking using data from other pagesHi everyone, I'm not a particularly frequent excel user, and I'm making a silly spreadsheet documenting the reviews my Australian friend is giving to British crisps. Each crisp has a picture on the home page, which when clicked takes you to the individual review of the crisp. She has ranked each crisp with different categories and given it an overall rating. I would love for each "overall rating" to go to a final page on the spreadsheet which shows all the crisps in ranking order. Now, of course I could just go through and type them in, changing them each time she reviews a new crisp, but is there a way excel can take this information with a formula? For example, on each individual crisps page the overall rating is cell D8. If a quaver was rated 8.5, a pombear was rated 6 and a mini cheddar was rated 7, could excel automatically put them in order of quaver, mini cheddar, pombear on my final ranking page? I appreciate this is a very long winded way of getting it across but it's a bit niche and so I wanted to try and make it clear! (It's much easier to just show someone a video of the spreadsheet but I don't think I can do that here!) Thanks!! submitted by /u/puzzled_puzzler12 [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#rows.com#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#financial modeling with spreadsheets#automation in spreadsheet workflows#no-code spreadsheet solutions#Excel#spreadsheet#chronological order#dates#function