Excel Experts, I need some assistance please

DarkSoul

DarkSoul

Mad Scientist
Mar 12, 2007
1,227
2
38
44
London, Ontario
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?
 
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.
 
Jaysn said:
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.
Click to expand...

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.
 
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.
 
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
 
Id have to do something like this, but this is wrong.... im so lost.

=Data!SUM($G$x-$H$x)
 
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.
 
darksoul said:
Also there is no actual C2 column, the formula for the "overall hardness" would be done on the chart alone.
Click to expand...

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.
 
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)
 
Is this 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. 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!
 
You must log in or register to reply here.
AquariaCentral.com
Top Bottom