need typo conditional formating code

[HCI]

im gonna do my best to explain this.

i am an estimator for a construction company. we get several bids from many different trades for each job we bid. (electrical, framing, drywall, etc.)
alot of times these bids are broken out in detail.

lights - $35,000.00
Wire - $5,000.00
Conduit - $3,500.00

sort of like that. but we will get several bids from electricians broken out the same way. eventually after entering in all the electricians you can see a trend in the prices for that job example ( lights will range from $25,000.00 to $35,000.00)

well the other day i made a typo and didnt catch it.

Electric company 1 | Electric Company 2 | Electric Company 3
Lights - $25,000.00 | $ 32,000.00 | $3,500.00 < Typo
Wire - $5,000.00 | $ 4,500.00 | $ 6,000.00


i hope you get the idea now.

my question is:

is there some kind of conditional formating code that will look at the range of numbers for a particular line item and determine if one just doesnt fit the trend of those numbers and then highlight that number to be double checked? like the $3,500.00 typo in the lights catagory.

thanks for any help.

[mbarron]

You could use something like this:
Assuming the figures are in B2:E2
Highlight B2:E2 and use the following formula as the CF formula

=AND(B2/MAX($B2:$E2)<0.5,B2<>"")

It will highlight any cell whose value is less than the 50% of the maximum bid.

[p45cal]

Perhaps you can base something on the attached.. it has conditional formatting based on values outside plus or minus x standard deviations.
There's a scroll bar to play with the number of standard deviations used.

Cells B2:D3 used this in conditoinal formatting:

=ABS(B2-AVERAGE($B2:$D2))>(STDEVP($B2:$D2)*$F$1)where F1 contained the number of standard deviations.

[Bob Phillips]

Pascal,

The scrollbar is a very nice touch!

I would remove the cell F1 and just use H1/100 in the CF formula. If you felt it important to maintain the visual aspect as 1.36 and so on, you coud format the cella s 0\.00.

Your formula can also highlight all quotes if the scrollbar goes low enough, which is a tad bizarre, they can't all be off the standard. Perhaps set the scrollbar minimum up a bit.

Anyway, my thoughts aside, a nice solution.

[p45cal]

Thanks for that xld.
Yes, it's very rare to be exactly average!