1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula Question

Discussion in 'Business Applications' started by aushaheed, Oct 9, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. aushaheed

    aushaheed Guest Thread Starter

    Joined:
    Sep 6, 2001
    Messages:
    85
    Hello:

    I am trying to figure a way to do the following.
    In the first row i want the months
    in the second row i want numbers from 100-184
    Now say in July the contents for #100 is 0
    Now say in Aug the contents for #100 is 11
    Now say in Sept the contents for #100 is 14
    Now say in Oct the contents for #100 is 52

    What i want the sheet to do is to take the difference in july and august and if the difference is less than 5 flag/mark it and if the difference is greater than 40 flag/mark it. then the next month when i enter the data for Sept it will take the difference in Aug and Sept and use the same rules to flag it less than 5 or greater than 40 for usage

    Any help would be great
     
  2. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    I am unsure of your setup but, highlight the column/rows you'll be doing the subtraction i.e. A1:A12 and go to Format/Conditional Formatting and select formula is and enter =(A2-A1)<5, select a Pattern color, click Add and enter formula is =(A2-A1)>40, select a color, click OK OK. Depending on your setup, you may have a use absolute references in the formulas.
     
  3. aushaheed

    aushaheed Guest Thread Starter

    Joined:
    Sep 6, 2001
    Messages:
    85
    Ok i am aware of the conditional formatting, but when i complete the properties and formula for one cell and edit, fill, right (or down) it doesn't change the formula to match the cells it's filling -- like you know when you do some formulas it changed the cells in the formulas to match the corresponding cells
     
  4. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I am kinda with Max - I got a little lost about the:
    Now say in July the contents for #100 is 0 part because I don't understand if there is just one number below July or many rows of numbers...... sounds like maybe you had rows from 100 to 184 but I couldn't tell.


    ....but two points regarding Max's solution (which should just what you want by the way)

    1) if in your setup you are wanting to flag cells that already contain formulas that give the differences - then just use the CELL VALUE part of conditional formatting

    2) if in your setup you are working with cells with values and use the FORMULA setup Max recommended, take care about where you formatting and where you start your range - it can fool you sometimes. I has me.

    Example:
    A1 is Jan with the value 5 below it in A2
    B1 is Feb with the value 9 below it in B2


    these continue on out for all the months

    the difference between JAN and FEB is > 5
    - what do you want flagged? JAN or FEB?

    If you select A1:A12 and conditional format =(B2-A2)<5
    it will flag JAN

    If you select A2:A12 and conditional format =(B2-A2)<5
    it will flag FEB


    You just have to be double check the logic you include in your formatting to be sure it does what you want. I have had done it in the past and it appeared to be working and when I realized it wasn't it stumped me for a bit till I went back and saw a tiny shift in cell designation was screwing me up!

    Just a tip

    - Castleheart
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I guess I was typing while you were aushaheed.

    If you use the format painter it should change all the conditional formatting formulas appropriately

    - C
     
  6. aushaheed

    aushaheed Guest Thread Starter

    Joined:
    Sep 6, 2001
    Messages:
    85
    OK HERE IS WHAT I HAVE:
    COLUMN A = IDENTIFICATION NUMBERS
    ROW 1 = MONTH
    -----
    B2 (JUL) = 1, C2 (AUG) =10, D2 (SEP) =14, E2 (OCT) =55
    -----
    I WANT IT TO START CALCULATING THE DIFFERENCE AT C2, SO
    =(C2-B2)<5, THEN I WANT THE VALUE IN C2 TO FLAG (HIGHLIGHT IN GREEN OR SOMETHING TO THAT AFFECT) BUT IF THE VALUE OF (C2-B2)>40 THEN I WANT THE VALUE IN C2 TO BE FLAGGED IN RED... I WANT THE VALUES TO BE FLAGGED NOT THE MONTHS -- WHAT I AM TRYING TO DO IS LIST ALL THE APARTMENT NUMBERS DOWN THE LEFT, MONTHS ON THE TOP, THEN WHEN I READ THE WATER METER EVERY MONTH I WANT TO PLUG THE NUMBERS IN THE RESPECTIVE FIELD AND IF THE WATER USAGE IS OVER 40 (WHICH IS REALLY 4000 GALS) THEN I WANT IT FLAGGED AND IF IT IS UNDER 5 (500 GALS) I WANT IT FLAGGED ANOTHER COLOR -- AM I MAKING SENSE?
     
  7. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Highlight B2 to whatever....

    FORMAT\ CONDITIONAL FORMATTING

    choose FORMULA and enter:
    =IF(B2="","",(B2-A2)<5)

    Hit the FORMAT button in that window and choose either FONT for font or PATTERNS for cell color. and pick a color. (Blue is a nice low water color, don't you think)

    Hit OK

    choose ADD and for condition 2 enter:

    =IF(B2="","",(B2-A2)>40)

    And select the color as before


    I am assuming that A2 is either blank or has a starting water meter reading.

    This will color the cells BLUE for less than 4 and RED for more than 40 and if the cell is blank leave it WHITE.

    Try it and see if it does for you.

    - C ;)
     
  8. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I just now caught that column A was Identification numbers.


    Same process but shift over to the right.

    Highlight C2 to whatever......

    ....and the formulas would be =IF(C2="","",(C2-B2)<5)


    all the same just moved over a bit


    sorry to be confusing :(

    - C
     
  9. aushaheed

    aushaheed Guest Thread Starter

    Joined:
    Sep 6, 2001
    Messages:
    85
    just for my info, what does ="","", mean?
    that's all i was missing :)

    thanks for your help
     
  10. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    It is looking for what is inside the quotes and not a numerical value.

    " DOG " would look for DOG in that cell

    In this case it is looking for "" ...... nothing! Emptiness!

    =IF(C2="","",(C2-B2)<5)

    =IF (C2 is nothing, then nothing, otherwise (C2-B2)<5)

    That insures that blank cells don't receive a conditional color - otherwise next months water meter reading (being empty) would mean that you used 0 gals - which is less than 5000 - and it would be colored BLUE.




    - :) C
     
  11. aushaheed

    aushaheed Guest Thread Starter

    Joined:
    Sep 6, 2001
    Messages:
    85
    Ok great this workbook is taking off... now a question .. i have 3 sheets -- one for meter reading, one that calculates the actual usage by calculating the diff in one month and another, and one that calculates the cost. When i enter the reading in sheet 1, it automatically calculates the difference and outputs it to sheet 2... how to i tell it not to print zeros or negative results when a month hasn't been read? Formula: =Reading!D4-Reading!C4

    Also, In my cost sheet (sheet 3) i am going to take the usage from Use! sheet and multiply it by 100 (since the numbers on the meter represent hundreds of gallons) then multiply by the usage and add a surcharge.

    =((Use!d6*100)*.06)+surcharge or i could simplify it to be:
    =(Use!D6*6)+surchage

    Now say the surcharge is subject to change. If i put the surcharge amount in the Use! sheet how to i create a formula that i will copy right then down that keeps the surcharge cell constant instead of changing it. For example:

    =(Use!D3*6)+SURCHARGE!C5
    =(Use!E3*6)+SURCHARGE!D5

    I want the SURCHARGE!C5 to stay the same because it's only in one cell....

    If you would like to see the sheet, please let me know.
    Thanks
     
  12. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> how to i tell it not to print zeros

    Tools -- Options -- View -- Window Options. Uncheck Zero Values.

    >> or negative results

    Or use conditional formatting. Cell Value Is <=0, Format -- Font -- Colour -- White.

    Or use a formula ; =IF(range<=0,"",etc.)

    For the last point use absolute references, eg:

    =(Use!D3*6)+SURCHARGE!$C$5

    Rgds,
    Andy
     
  13. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    aushaheed,

    <=(Use!D3*6)+SURCHARGE!$C$5>


    FYI:

    "$" is the global character. Use it before a number or letter and it will remain constant when copying or moving formulas.

    examples:

    $C$5 drag it and it is always C5
    C$5 drag it and it can become D5, E5, F5, etc
    $C5 drag it and it can become C6, C7, C8 etc


    And with regards to your SURCHARGE .... The Formula XLG put forth, points out an important practice. It is generally the best form in EXCEL to locate factors or "constants" that are to be a part of your calculations (and yet subject to change) in cells that are fixed and then referred to by all such formulas.

    These values can then easily be changed when necessary. Prices - percentages - factors - all these can be listed and labelled in a neat format somewhere on your sheets and with a few clicks, you have basically changed a zillion formulas.

    - C
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/170654

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice