Tech Support Guy banner
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
1 Posts
Discussion Starter · #1 ·
Okay first off with the simple question:

I know there is conditional formatting with excel, but it only lets me to set 3 conditions. Is there anyway to add more?

Mainly something like this
<=1 ------- Red
between 1-4 -----------Orange
between 3-6 ------------Yellow
= 6 -------------YellowGreen
IF(SUM(B1:F1)=36, Green)

Look at the follow question and click the link to a good idea what I want.

Advanced:
I am looking for tips, hints, formulas, comments, suggestions for the following.
hostima.net/paroxsitic/excel.html
It would be insanely awesome if someone could actually get a start on my ideas and post a link to a working copy, I might even spit out some money for it.

Thanks alot.
 

·
Registered
Joined
·
126 Posts
First, the conditional formatting which you mentioned lets you set 3 conditions, which actually gives you 4 possibilities - the default and the 3 formats you set. Unfortunately, you want 5, which is totally beyond the realm of conditional formatting. Now, you can still do this, but you'll have to use VBA and, depending on how big the area of cells you want to format is, it could impact the speed of the sheet refresh. So I took a look at your link so I could get a better understanding of your requirements.

I think this is feasible, but I've got some questions about your formula. You said, "Cracked is worth 1 point. Chipped worth Cracked x 1.5, Flawed worth Chipped x 1.5 ..etc." Now, if I follow the description exactly, the formula becomes
B5+(C5*B5*1.5)+(D5*C5*B5*1.5)+(E5*D5*C5*B5*1.5)+(F5*E5*D5*C5*B5*1.5)
(parenthesis are superflous, but they make it easier to see the various factors)

The formula you gave

C5+1*1+D5*(C5+1*1.5)+E5*(D5*1.5)+F5*(E5*1.5)+G5*(F5*1.5)-1

does not reflect the description. First of all, the formula seems to be one column off from your example. But if I move everything to the left one column, it gives the same results as in the example. If I understand your intent, it should be

B5+(C5*B5*1.5)+(D5*E5*1.5)+(E5*D5*1.5)+(F5*E5*1.5)

What I had originally interpreted from your description was

B5+(C5*1.5)+(D5*1.5*1.5)+(E5*1.5*1.5*1.5)+(F5*1.5*1.5*1.5*1.5)

Interestingly, these formulas give quite a variation/

Formula 1 Yours Formula 3 Formula 4
Apollo 229 63 70 49.5625
Commander 13992 213 222 79.125
env3zer0 3 48 66 52.5
Gawd 8160 186 195 63.9375
killing_time_ 951 88.5 78 34.3125
LilCommander 12048 204 213 74.0625
metalhead91082 10104 195 204 69
Multi_Commander 0 0 0 0

Formulae 1, 2, and 3 (in this limited sample) give the same relative rankings to the eight participants. But Formula 4 give a different ranking, most because of the exponentially higher value of the better gem pieces.

I guess the bottom line is I need to know a couple of things:
1) How big is the player pool you expect?
2) What is the correct interpretation of the formula?
Other than that, it should be relatively easy to set this up in VBA.
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top