User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > General Discussions

Reply
Thread Tools Display Modes
Unread 3 Jul 2006, 15:12   #1
Hicks
Raaaaaaaah!
 
Hicks's Avatar
 
Join Date: Apr 2000
Location: United Kingdom
Posts: 2,296
Hicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like him
Excel Wizkids Help !

Bit of a help me with my homework thread here but I'm not sure where else to look for answers since the Excel helpfiles are dire. At the moment I'm writing my dissertation on Chinese manufacturing trading patterns with the Developed World, I'm just putting the finishing touches to my dataset in Excel, however, I'm not that good with the program and I can't work out how to do these two things (Questions are in bold for those who don’t want to read the rest):

(1) I want to work out the domestic consumption in each sector, to do this I'm using domestic production - exports + imports, my problem is that my figures for domestic production are in millions of domestic currency while my figures for exports and imports are in thousands of PPP $. To convert production into PPP $ I'd use the formula =[Domestic Production]*[1000]*[PPP Exchange Rate], easier said than done ? Well not exactly, there's 3000 entries in my dataset which would mean typing that in 3000 times (No way is that happening), is there anyway I can multiply an entire column of data by 1000 and then by another column of data all at once as typing it out manually isn't realistic ?
(2) I'm using wages as a proxy for human capital, for each industry I'm taking wages and salaries paid and dividing it total employment in that industry then I'm dividing this figure by the result for total manufacturing and multiplying by 100 to get the numbers in index form, this works fine for everything except the total manufacturing industry which I ideally want to read 100 in each column since it’s the base of the index however I can't seem to make it do this because it says its a circular reference, is there anyway I can make it display 100 without altering the data in these cells since they are used to calculate all the other cells ?

Any help would be great cheers mates !
__________________
Hicks
Mercury & Solace
Always [Fury]
Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 15:24   #2
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel Wizkids Help !

Quote:
Originally Posted by Hicks
is there anyway I can multiply an entire column of data by 1000 and then by another column of data all at once as typing it out manually isn't realistic ?
I don't relally understand this. Why can't you do it once and then use fill down?
Quote:
is there anyway I can make it display 100 without altering the data in these cells since they are used to calculate all the other cells ?
I suspect the easiest way to do this would simply to have two columns and then hide one of them. The hidden one would have the data you need for calculations, the visible one would be the index one. It's the same sort of way you'd show percentage values.

There might be a way doing it by messing around with cell formatting, but I'm not sure that would be straight forward.
Dante Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 15:26   #3
meglamaniac
Born Sinful
 
meglamaniac's Avatar
 
Join Date: Nov 2000
Location: Loughborough, UK
Posts: 4,059
meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.meglamaniac has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel Wizkids Help !

Not sure about 2.
As to one, can you clarify?

So you want to multiply each cell in that first column by 1000. Ok easy enough.
Then what? You want to multiply each cell with a corresponding cell in the second column (eg. =SUM(A1 * 1000 * B1), or by the total of the second column, or something else entirely?
__________________
Worth dying for. Worth killing for. Worth going to hell for. Amen.
meglamaniac is offline   Reply With Quote
Unread 3 Jul 2006, 15:29   #4
Ste
Bored
 
Ste's Avatar
 
Join Date: Apr 2001
Location: Nottm ->Shef ->Croydon ->Manc ->Durham ->Sheffield
Posts: 6,506
Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Ste has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel Wizkids Help !

for 2 you could make it simply round to the nearest 100. It should keep the "exact" result the same.
__________________
Wise men write because they have something to write about; fools write because they have to write something. - Plato

yeh so Plastic Brilliance is now known as FOXYSTOAT - Come on by and check it out!
Ste is offline   Reply With Quote
Unread 3 Jul 2006, 15:31   #5
Hicks
Raaaaaaaah!
 
Hicks's Avatar
 
Join Date: Apr 2000
Location: United Kingdom
Posts: 2,296
Hicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like him
Re: Excel Wizkids Help !

Quote:
Originally Posted by Dante Hicks
I don't relally understand this. Why can't you do it once and then use fill down?

I suspect the easiest way to do this would simply to have two columns and then hide one of them. The hidden one would have the data you need for calculations, the visible one would be the index one. It's the same sort of way you'd show percentage values.

There might be a way doing it by messing around with cell formatting, but I'm not sure that would be straight forward.
I probably didn't explain it very well, I already have values in each of the cells, if I go to the first row and type in the formula I want it would be "=2896772*1000*H5" but then if I go to fill down it deletes the data in the second cell and replaces it with the 2896772 so I have the same number going all the way down. I guess what I need to to protect the data value in each cell and filling down only the formula. I guess again I could put all the figures in domestic currency on another sheet and have =sum(Sheet2.A1*1000)*H5 (Or however the formula goes to grab data from another sheet) but I would have prefered to be able to keep it all on one sheet.

Yea I was thinking I could have the real values on a second spreadsheet out of the way out of the way then link them in and then just write 100 on the total manufacturing section was just wodnering if there was some sort of techy format to get round doing that. Also rounding may work, I shall give it a go now again I'm going to sound like a total idiot here but how do you round numbers to the nearest 100 ?
__________________
Hicks
Mercury & Solace
Always [Fury]

Last edited by Hicks; 3 Jul 2006 at 15:43.
Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 15:45   #6
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel Wizkids Help !

Quote:
Originally Posted by Hicks
I probably didn't explain it very well, I already have values in each of the cells, if I go to the first row and type in the formula I want it would be "=2896772*1000*H5" but then if I go to fill down it deletes the data in the second cell and replaces it with the 2896772 so I have the same number going all the way down. I guess what I need to to protect the data value in each cell and filling down only the formula.
I'm still not quite grasping the problem here, but it sounds like you should use intermediate columns to do some of the work for you.

e.g. imagine you're trying to do calculations like the following:
Code:
Salary_Per_Day			Outgoings_Per_Year      Money_Left_Over_Per_Year
40				2000
60				3000	
75				6400
Instead of messing around with the first column, it's much easier to create another column called Salary_Per_Year and then times column 1 by 365 (or whatever). Don't worry how it looks, you can just hide the columns you don't want to print / export.
Quote:
Also rounding may work, I shall give it a go now again I'm going to sound like a total idiot here but how do you round numbers to the nearest 100 ?
According to this : http://exceltips.vitalnews.com/Pages...arest_100.html you can't, at least not using formats. You can of course use a formula, explained here : http://www.wcape.school.za/subject/C...roundindex.htm
Dante Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 15:56   #7
Hicks
Raaaaaaaah!
 
Hicks's Avatar
 
Join Date: Apr 2000
Location: United Kingdom
Posts: 2,296
Hicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like himHicks is an inspiration to us all and we should try to be more like him
Re: Excel Wizkids Help !

Oh Problem 1 is sorted, thanks Dante, I just created another column which I can hide on printing, I'll look into the rounding bit now.
__________________
Hicks
Mercury & Solace
Always [Fury]
Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 16:16   #8
hyfe
Dum Di Dum Di
 
Join Date: Sep 2001
Posts: 858
hyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet society
Re: Excel Wizkids Help !

If anyone know how to do 1 without resorting to intermediate columns I'm really interested though.. it's been bugging me for some time.
__________________
Ni! M00!
my boring homepage
hyfe is offline   Reply With Quote
Unread 3 Jul 2006, 16:42   #9
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel Wizkids Help !

Quote:
Originally Posted by hyfe
If anyone know how to do 1 without resorting to intermediate columns I'm really interested though.. it's been bugging me for some time.
Well couldn't you do a search/replace and simply change the formula? Normally if I want to modify a lot of Excel data I find it a lot simpler to just copy and paste the stuff into a text editor and do most of the work there. (You'd have to enable "view formulas" in this example of course).

Normally though it's probably not a good idea to store variables in that kind of way as it means you have problems like this.
Dante Hicks is offline   Reply With Quote
Unread 3 Jul 2006, 17:38   #10
hyfe
Dum Di Dum Di
 
Join Date: Sep 2001
Posts: 858
hyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet societyhyfe is a pillar of this Internet society
Re: Excel Wizkids Help !

Oh, there's always solutions, I just want one that doesn't so feel so, well, stupid..

The simplest example which bugged me was when I had a bunch of raw-data and needed to multiply them with 1.15.. and due to export-macro'es I needed it in the same place. Dragging a formula to multiply by 100 and then copy/pasting back just seemed so.. primitive.. and it was just once, so creating formulaes or fixing separation of raw-data/export-data would have just been needless work.
__________________
Ni! M00!
my boring homepage
hyfe is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Forum Jump


All times are GMT +1. The time now is 13:51.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018