Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom

It's free to register, to post a question or to start / join a discussion

View Forum Rules | Contact Forum Editor

 

Some Excel help.

1 3 4 5 … next » last »

gengiscant
Resolved

Likes # 0

Posted October 28, 2011 at 10:15AM

I would like to set up an Excel spread-sheet to keep a track of my weight which is quite important at the moment. Its quite a simple sheet in that it only has three columns. so am looking for formulas to do the following.

The first line has the date, I need that to change monthly and yes I could do that each month manually.

The second is current weight.I will obviously need to input that monthly.

The third is change over the month.

The fourth is the overall change a figure that will change over the months.

I hope I have explained myself, this is a rare foray into spread-sheets and to be honest I have not got a clue really.

Like this post
marvin42

Likes # 0

Posted October 28, 2011 at 10:40AM

If I understand you correctly - the first line is Jan, Feb, Mar etc. In A1 type Jan and then put the cursor on the bottom right corner of A1 so it changes to a solid cross, hold down the right mouse button and then drag across to L1. This will give you all the months (it will work whatever month you type into A1). You can then enter the current weight in A2, A3 etc.

In B3 type =A2-B2 and then drag across as for the months. This will give you the change from the previous month.

In B4 type =($A$2-B2) and copy across as above. This will give you the overall change.

Like this post
marvin42

Likes # 0

Posted October 28, 2011 at 10:42AM

Correction "You can then enter the current weight in A2, A3 etc." should read "You can then enter the current weight in A2, B2 etc."

Like this post
gengiscant

Likes # 0

Posted October 28, 2011 at 10:46AM

Sorry marvin42 it should be column not line. I will be working top to bottom.

Like this post
gengiscant

Likes # 0

Posted October 28, 2011 at 10:49AM

Got the months sorted so far thanks to marvin42.

Like this post
marvin42

Likes # 0

Posted October 28, 2011 at 10:52AM

OK so still Jan in A1 but copy down to A12.

C2 =B1-B2 and copy down.

D2 =($B$1-B2) and copy down.

Like this post
gengiscant

Likes # 0

Posted October 28, 2011 at 11:07AM

Sorry marvin42, you will need to explain in a little more detail for column C and D.

What do I do with these formulas? Here is a screen-shot of my sheet. Excel

Like this post
gengiscant

Likes # 0

Posted October 28, 2011 at 11:27AM

Sorry again column C should be titled monthly change.

Like this post
compumac

Likes # 0

Posted October 28, 2011 at 12:18PM

Do you want column D to reflect change from first occasion or from the previous month- because you can have columns for both?

Like this post
gengiscant

Likes # 0

Posted October 28, 2011 at 12:23PM

Compumac. I want column C to show change from the previous month and Column D to show the overall change from the first month.

Like this post
compumac

Likes # 0

Posted October 28, 2011 at 12:38PM

To show change from the current weight contained in B3 and insert the change in C3 In C3 put “=B3-B2” WITHOUT INVERTED COMMAS Then copy that formula down in that column as far as you need to go.

To show the change from the start contained in B2 and show that in column D, you have to refer to the initial weight i.e. B2 as an absolute reference. So in D3 put “B3-$C$3” the $ being the absolute reference and maintaining that reference. Then copy that formula down that column as far as you need to go.

If you type “B3-C3” and place the cursor between the letter C and the numeral 3 and press F4 it will insert the absolute reference for you.

Make sense?

Like this post

1 3 4 5 … next » last »

Reply to this topic

This thread has been locked.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.