Excel Help

Monaou

Hi All,

I have a problem in MS Excel that is costing me hours and hours of tedious calculations, so any help would be greatly appreciated!!

The problem is that I have a column of numbers (2,500 observations) that vary randomly from negative to positive sign. What I need to do is add all the groups with the same sign eg. three negative figures after each other must be added, two positive ones etc. If there is only one negative then one positive then nothing has to be done. So far I am doing this manually, but if there is anyway to setup an IF Statement or something like that to calculate it for me then that would be an unbelievable help.

If anyone has any idea how to do this, or even knows a good web site on Excel help could they please pass it on.

Any help would be brilliant!!

Chalk

might be helpful to post a sample excel file,
i could probably figure it out but i cant get my head around what your trying to do

Monaou

Chalk wrote:

might be helpful to post a sample excel file,
i could probably figure it out but i cant get my head around what your trying to do

Yes, imagine this would help alright.....!!

Eg. Add -0.44 + -1.05, don't do anything to -1.05 and 0.30 (cause not of same sign), do nothing with 0.30 and -1.0 (not same sign), add -1.01 and -0.173 etc etc. All group of numbers (sets of two, or three etc) must be added together once they have the same sign (all positive or all negative).

-0.440230
-1.054422
0.309385
-1.010966
-0.173100
0.121380 Add these three positive numbers together.
1.298926
0.095106
-0.829230 Add these four negative numbers together.
-0.749064
-0.210619
-0.598012
0.221180 Add these two positive numbers together.
0.097105
-0.291031
-0.203432

Hope this helps to explain the situation a bit better, get back to me if it need further explaining. Thanks!

Khannie

First off: Welcome to boards.

Hmmmm. What your suggesting is defo possible, though I'm not sure how to do it with excel.

Here's how I would do it: (remember that lack of excel knowledge)

Save the file as a .csv (you can then open this in notepad or whatever)
Write a perl script to do what you want.

If you want me to do it, I have 30 minutes to kill.

derek27

i'd be thinking that this problem could be easier to tackle if you organise your data differently on the spread sheet. ie, keep the neg values in a different column from the positive ones...

parameter 1..+0.014
parameter 2..+0.789
parameter 3............-0.028
parameter 4............-0.384
parameter 5..+0.418

etc etc

analysing the data and performing functions on it would surely be easier if you did this?

Monaou

Thanks for the welcome Khannie!

I'm afraid I have no knowledge of Perl script at all....don't even know what it is! If it's easy to get your head around then any tips here would be brilliant, or if your up for the challenge then can definitely send it on to you, but not sure you want to subject yourself to that!! Have also realised that I have to add up figures within groups as well (eg. group of four negative numbers...must add first two, then first three, then all four) .

I have already done what derek27 suggested, and it helps alright, but still an awful lot of calculations to do.

Any suggestions??!!

Khannie

I'm leaving at 6.30. You can email me the excel file and I'll send you back what you're looking for.

khannie at
unreal
dot ie

Just be very specific about what you want. I'll only do up one script tbh. I'll mail you back an excel file with all the relevant calculations.

I just want to get this straight. If I had the following:
-1
1.5
-2
-1
1

it should give ("|" denotes new cell:
-1 | -1
1.5 | 1.5
-2 | -3 (i.e. -1 + -2)
-1 | -4 (i.e. -1 + -2 + -1)
1 | 2.5 (i.e. +1.5 +1)

Is that right? If not, send on the calcs that you've already done in the excel file you send me.

EL_Loco

hello,

I've thrown something together, it's not a full solution, but I'm just looking through the functions available. it'll make your life a bit easier though if you want to have a look. PM an email address. formatting is a bit messed up. the positive and negative fields can be hidden, they're for using in the condition for adding the numbers. only checks 2 of the numbers at a time at the moment. last thing I need is something to add up as far as a "do nothing". unless at this point you're looking for a grand total. do you need a "negative total" and "positive total"?

-0.44023  negative     -1.494652
-1.054422	negative	do nothing
0.309385	positive	do nothing
-1.010966	negative	-1.184066
-0.1731	     negative   	do nothing
0.12138	positive	1.420306
1.298926	positive	1.394032
0.095106	positive	do nothing
-0.82923	negative	-1.578294
-0.749064	negative	-0.959683
-0.210619	negative	-0.808631
-0.598012	negative	do nothing
0.22118	positive	0.318285
0.097105	positive	do nothing
-0.291031	negative	-0.494463
-0.203432	negative	do nothing

EDIT: thanks khannie, sure I usual just read the forum as you can see by the post count!

Khannie

Jaysus, welcome back loco. Thought we'd lost you forever there for a while.

derek27

there is a SUMIF statement in excel for working with large amounts of data. i suggest you use ms excel help file to explore this. you can discover how to write the argument in the function bar and then paste it into every location that requires a calculation. basically youwant the function to check whether itis greater than or less than zero before it performs any operation on it. i haven't explored its use myselfbut i think this will go a long way to sorting out your task. check the net too for advice on how to use the function.

EL_Loco

ok I got it working. you better still need this or there'll be trouble I delved into a bit of excelVB and after concocting various over the top methods, I have fallen back onto about 15 lines of code, which seem to be doing the job.

first column is your input, second is the macros I threw together doing the calculation, 3rd column is the check doing it by hand in excel.

-0.44023		
-1.054422	-1.494652	-1.494652
0.309385	0.309385	
-1.010966		
-0.1731        -1.184066	-1.184066
0.12138		
1.298926		
0.095106	1.515412	1.515412
-0.82923		
-0.749064		
-0.210619		
-0.598012	-2.386925	-2.386925
0.22118		
0.097105	0.318285	0.318285
-0.291031		
-0.203432		

Seems to be holding together alright. send me over your 2,500 entries and well see how it copes.