Forum Discussion
Enumerating Smartly in Excel
Hi I have an excel question - I need to have the function be smart enough to do two things:
1. Determine the first integer of a value (ex x.xx) using Report Category column (SEE BELOW) based on 1-5 Values
2. Find the largest value in the column with the numbers in it and add .01 to it. This is the hard part as the first integer needs to be taken into consideration first and then look at the trailing 2 integers after the <.> to get the value to start with then add the .01 to it.
Below is an example of what I have so far showing what I need - NOTE IT WILL NOT BE IN PERFECT ORDER BY THE REPORT ID - IS FOR THIS EXAMPLE ONLY:
I used this function to get the first integer =IF($D4="External Client Regulatory","1.",IF($D4="External Operational Request","2.",IF($D4="Evolent Standard Operational","3.",IF($D4="Evolent Vendor Operational","4.",IF($D4="Evolent Internal Operational","5.",0)))))
But after that I can't figure out what to do to get the correct trailing numbers. It does not need to be dynamic as we often times add and delete rows from the output so the numbers should stay the same once they are assigned.
Report ID Report Name Report Category
1.01 | Anthem & EMBLEM Call Center Stats Report | External Operational Request |
1.02 | Anthem & Emblem Care Program Details Report | External Operational Request |
2.01 | Anthem & Emblem CMART | External Client Regulatory |
2.02 | Anthem Newborn Kick Payment | External Client Regulatory |
5.01 | Internal Anthem Weekly Auths File | Evolent Internal Operational |
5.02 | Internal Emblem claim denial and adjustment details | Evolent Internal Operational |
=IF(D4="External Client Regulatory","1."&COUNTIF($D$4:D4,"External Client Regulatory"),
IF(D4="External Operational Request","2."&COUNTIF($D$4:D4,"External Operational Request"),
IF(D4="Evolent Standard Operational","3."&COUNTIF($D$4:D4,"Evolent Standard Operational"),
IF(D4="Evolent Vendor Operational","4."&COUNTIF($D$4:D4,"Evolent Vendor Operational"),
IF(D4="Evolent Internal Operational","5."&COUNTIF($D$4:D4,"Evolent Internal Operational"),0)))))
Maybe with above formula which i applied in the attached file.
6 Replies
- OliverScheurichGold Contributor
=IF(D4="External Client Regulatory","1."&COUNTIF($D$4:D4,"External Client Regulatory"),
IF(D4="External Operational Request","2."&COUNTIF($D$4:D4,"External Operational Request"),
IF(D4="Evolent Standard Operational","3."&COUNTIF($D$4:D4,"Evolent Standard Operational"),
IF(D4="Evolent Vendor Operational","4."&COUNTIF($D$4:D4,"Evolent Vendor Operational"),
IF(D4="Evolent Internal Operational","5."&COUNTIF($D$4:D4,"Evolent Internal Operational"),0)))))
Maybe with above formula which i applied in the attached file.
- NewelleNielsen8821Copper ContributorYes this works perfectly - just need to get the formatting so it will be two digits behind the . so thanks again!
- Riny_van_EekelenPlatinum Contributor
NewelleNielsen8821 Perhaps wiser not to hard-code all the category names with nested IF statements. In the attached file, I've demonstrated the use of a lookup table and also resolved the two-digits issue after the point.