Excel Experts, I need some assistance please

[DarkSoul]

Im setting up a spreadsheet to keep track of maintainence, including charts.

The last thing i want to do is add a KH, and GH column, with a chart showing the trends of the hardness changes (if any)

I know how to make the chart, and get the individual results to display on the chart, but i also want to add a third "series" which would be the overall hardness, or GH - KH = overall hardness (or vice versa... im still looking into that equation :))
the problem is im not sure how to tell the chart to use the GH and KH column the right way for the chart to display its trends.
I would need to subtrac one column from t he other, but only by individual rows, instead of the entire column.

Anyone know how to do this?

[Jaysn]

Wouldn't a simple formula work? If your "overall" column is C, GH is A, and KH is B, then for each row in C your formula would be "=Ax-Bx". Drag that formula down to auto pop the rest of the column, and you should be set. When you create your chart, select A, B, and C, and it should be what you are looking for.

[DarkSoul]

Wouldn't a simple formula work? If your "overall" column is C, GH is A, and KH is B, then for each row in C your formula would be "=Ax-Bx". Drag that formula down to auto pop the rest of the column, and you should be set. When you create your chart, select A, B, and C, and it should be what you are looking for.

Im not totally following you on this one.

mostly the formula .... i dont know excels syntax, so i dont know how to tell it exactly what I want to do.

Currentlly anything i tried simply results in excel giving me a total number for ALL of the values enterd.

for instance, if i have

A B C
5 4
7 3
8 8


and I try to setup a formula all i end up with is something that tells me the "final value is 35" instead of giving me 3 answers in column C.
In the above example the correct data I would be looking for would be

A-B=C
5-4=1
7-3=4
8-8=0

and i dont want to have to make a new formula each time i add new values to new rows. with the other charts, i simply fill in the data, and it does it for me, all the way down the sheet.

[jessicar613]

I'm not sure if I understand the problem you're having- you should be able to enter the formula Jaysn gave you ( "=Ax-Bx") into C2, it will fill in the number one, then drag the little corner thingie down and it will autofill the rest of column C.

[DarkSoul]

Well part of the problem is, I may be having difficulty explaining what Im trying to do... which happens often, also, I dont know these formulas or how to use them properly, so when im told to enter Ax-Bx into C2, thats what I do (obviously using my values, and not these example values) and it doesnt do what i want it to do.

Also there is no actual C2 column, the formula for the "overall hardness" would be done on the chart alone.

i dont know what formula im supposed to be entering where or how.

on my chart I have this now, but it isnt showing anything on the chart, and this is for only a single row.... if i want to add others I need to do it manually, setting up the formula for each row, instead of just entering a new number on a new row, and having excel do the rest.

=Data!$G$3:$H$3

[DarkSoul]

Id have to do something like this, but this is wrong.... im so lost.

=Data!SUM($G$x-$H$x)

[DarkSoul]

here is my current spreadsheet, including charts. (Thanks to root81 providing his original work for me to mutilate :))
http://users.mnsi.net/~darksoul/Aquarium Log Sheet.xls
I have removed the formula for the permanent hardness (GH - KH) .... and ive come to realize that permanent hardness is actually .... pH ??? if it is such, then i am indeed a fool lol.

[Jaysn]

Also there is no actual C2 column, the formula for the "overall hardness" would be done on the chart alone.

That's your problem right there. Excel does funny things when you try to have it run a formula in a chart IME. Add a column for your overall hardness in your source data. You can hide the column if you don't want to see it, but it's easier if it's there. I'll play with the file a bit tomorrow when I get back to work and see if I can get it looking like you want.

[DarkSoul]

so you understand what i mean then?the hardness chart should have 3 series in it... the GH, KH and the permanent hardness. (GH-KH)

[Jaysn]

Is this (http://home.comcast.net/%7Emyrddraal1/Aquarium_Log_Sheet_2.0.xls) what you are looking for? I changed your graphs to lines instead of bars, they make trending a lot easier. Feel free to change them back though.

I also made a third version, here (http://home.comcast.net/%7Emyrddraal1/Aquarium_Log_Sheet_3.0.xls). The charts are the same on this one, but there's conditional formatting applied to the data tab using your max level indicators, and a change column for each parameter. The main column turns red if the entries are greater than your max level, while the change is red or green depending on negative or positive changes. Let me know what you think!

[DarkSoul]

hey that third one is purty :)

I think its what i was looking to do..... looks right anyway.
the max lvl row up top was just a reminder really... wasnt going to be a part of the final sheet, and the lines do ineed look better.
i just couldnt find the right colours to go with what I had for the background.
I like the way it looks so i'll leave it that way.

the peranent column, isnt quite hidden, if i print this out, its there, just without borders.
also, that does achieve the goal i had in mind, but not the way i wanted to do it (if even possible)

as you can see from the other values, it will do the calculations all the way down every row, simply by me entering the information in. with the GH and KH it will do that until the 0's stop... at which point i have to add a bunch of other formulas to go further down the sheet.

That may not even be a problem, since the way its setup it would only be one sheet per month...... so I would only need a finite number of formulas.

btw thanks for the help.... id otherwise be totally stuck at this point... and without pretty line charts with good colours :D

[Jaysn]

You're welcome! I didn't hide the permanent column but it's really easy if you want to. Select the entire column, then right-click and select Hide. You can drag the formulas by grabbing the little square box at the bottom right corner of each cell. Drag it down and it will recreate the formula in the same pattern.

[DarkSoul]

You're welcome! I didn't hide the permanent column but it's really easy if you want to. Select the entire column, then right-click and select Hide. You can drag the formulas by grabbing the little square box at the bottom right corner of each cell. Drag it down and it will recreate the formula in the same pattern.

I didnt know that :)

i tried hiding it, but when I hide the permanent column, it also hides the data on the charts.