# Excel Formula Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### aushaheed

##### Guest
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

#### maxflia10

Originally posted by aushaheed:
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
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.

#### aushaheed

##### Guest
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

#### CastleHeart

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

#### CastleHeart

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

#### aushaheed

##### Guest
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?

#### CastleHeart

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

#### CastleHeart

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

#### aushaheed

##### Guest
just for my info, what does ="","", mean?
that's all i was missing

#### CastleHeart

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

#### aushaheed

##### Guest
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

#### XL Guru

>> 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

#### CastleHeart

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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On