Consulting

Results 1 to 5 of 5

Thread: need typo conditional formating code

  1. #1
    VBAX Regular
    Joined
    Dec 2009
    Posts
    11
    Location

    need typo conditional formating code

    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.

  2. #2
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Thanks for that xld.
    Yes, it's very rare to be exactly average!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •