• Macro for conditional formatting, ranking, etc. (Excel 2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro for conditional formatting, ranking, etc. (Excel 2003)

    Author
    Topic
    #437318

    I am working with large spreadsheets where there might be 10,000+ rows. There will be three main columns: Company, Zip Code, and Price. The same ten companies will be repeated over and over in same order for 1000+ zip codes, with a price for each company in each zip code. I’d like to be able to set up some sort of macro or process to do the following (over and over again, for the ten rows in each of the 1000+ zip codes):

    (1) Add a column that ranks the companies 1 to 10 (ascending) by their Price.
    If our company is 1, 2, or 3, turn that row green.
    If our company is 8,9,10, turn that row red.

    (2) Add a column that shows Price difference from the MIN for that Zip Code.
    Add a column that shows Price difference from the MAX for that Zip Code.
    Add a column that shows Price difference from the MEDIAN for that Zip Code.
    Add a column that shows Price difference from the AVERAGE for that Zip Code.

    (3) Add a column that shows Price % difference from the MIN for that Zip Code.
    Add a column that shows Price % difference from the MAX for that Zip Code.
    Add a column that shows Price % difference from the MEDIAN for that Zip Code.
    Add a column that shows Price % difference from the AVERAGE for that Zip Code.

    These are all things I can do easily enough for one group of tne rows, but my efforts to extend these processes to a second group have not succeeded, much less to 1000+ groups.

    If I can figure out a way to do the above, my next step would be that I’d like to go through the worksheet by company and show each companies average for each of the above columns.

    Companies are currently listed by name, but it would certainly be possible to assign number values to them in an extra column if that would make this easier.

    Any suggestions from anyone out there would be greatly appreciated!

    Viewing 1 reply thread
    Author
    Replies
    • #1039252

      Welcome to Woody’s Lounge!

      I think this can be done with formulas – see the attached example. All formulas were created in row 2, then filled down as far as the data go.

      • #1039265

        Appreciate the solution Hans.

        I wonder, though, what happens with the MIN when one of the top 10 has a zero value. OP didn’t indicate one way or the other, but I know that on some projects I have worked on, there was a possibility that 0 occurred in the ranking, in which case the the MIN would have to be altered to be “MIN non-zero solution”. Just a thought.

        • #1039268

          You’d have to use an array formula (confirmed with Ctrl+Shift+Enter) looking like this:

          =MIN(IF(range>0,range))

      • #1041024

        This worked perfectly — many thanks!

    • #1039276

      Many thanks, I will give these a try and see. Much appreciated!

    Viewing 1 reply thread
    Reply To: Macro for conditional formatting, ranking, etc. (Excel 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: