Build Your Own Arcade Controls Forum
Main => Software Forum => Topic started by: hypernova on July 17, 2006, 05:48:48 pm
-
This has nothing to do with arcades, but at work, I've put together a spreadsheet with various rates from many trucking companies we use/have used/may use in the future. There's a total of around 8 or 9 on the spreadsheet. What I've done, is take a single class and weight, and got the rate for numerous zip codes. (Around 70.) So there's around 600 or so fields. Now a few of the companies only service certain parts of the nation, so it's really probably around 350-400 or so fields of data.
What I've done so far is take the lowest rate in each row, and shade it yellow. I then took the next ones within $25 and shaded them red. The ones within $50 were purple, and the ones within $75 were gray. I also did this the hard way. One by one. This is quite the timesucker. Now every so often, a new company wants in on some business, or our existing companies redo their discounts, and I have to go in, and change all the prices (not a big deal,) and ALSO potentially change all the color coding if they end up being the new minimum price in a certain zip. (A big deal. Lots of time analyzing, and changing colors, one after the other.)
So I know Excel has an option to automatically highlight and do things like I'm doing, but much faster, and when I change the prices, will update the colors automatically. I started looking through the help section, but was unable to get the MIN working. I did what it said for highlighting the minimum, but it kept highlighting the entire row instead of just the lowest price. =MIN(E4:E12) was the coding it told me to use, which I did. What happened?
Also, I figure I need to use "IF" formulas for the other fields, but not quite sure how. Can anyone let me know how to do this right? I need the minimum price in a row to be one color. Anything within $25 another color, anything within $50 another color, and anything within $75 another color. Also, if there's an option, to make blank fields another color (actually it'll be a pattern, but anywho...) Thanks!
-
if you type the formula =MIN(E4:E12) into a completely seperate cell, it should then display the minimum value out of the range E4 - E12. That has absolutely nothing to do with changing the colour of the cell.
In short, it would be quite a pain in the butt to try and do what you want to do, without trying to code a macro, which would then involve scripting etc...
i do not know exactly how proficient you are with programming, but if you are do a search for "excel macro code" and you should come up with a few helpful sites
-
You should be able to achieve this using a conditional format uner the Format Menu. I have attached a picture of the sample formula that can be extended to any amount to continue to add $25 increments. I also used the min command to arrive at the lowest number in the row and then use tha as my base before incrementing by $25. I think this answers your question.