Rk,Player,Salary
14,Sonny Weems,$736,420
15,Pops Mensah-Bonsu,$242,793
16,Quincy Douby,$85,519
1,Tracy McGrady,$23,239,562

I tried to import this into spss but because the file uses a comma delimter and the salary values also have commas, I ended up with two extra variables and no meaningful salary data. Can someone suggest a method to get the salray data intact. You can try to import the above as a csv to see what I mean.

Also I think because of the dollar sign spss counts the salary variable as a string, how can I remove the dollar sign from all the variables. Also how do I use if else statemnts in the compute variable menu.

Remove the commas from the salary, ldo

there are five hundred and somethign entries

Either change the separator when the file is created or use the concatenate function after you import the file to put the salary back together.

there's gotta be a cleverer way than just manually changing the separator for each entry. I dunno much bout programming or anything, but I can't concatenate cause spss reads smaller numbers as having missing values for anything in the hundreds,tens and ones digit and reads 000 as just 0.

I was gonna write conditionals like
if v5 is a missing value wage=salary*1000 + V4*1
else wage=salary*1000000+v4*1000+v5

I think this would work except that the salary values have a dollar sign attached so I think it reads it as a string and I do not know how to write an if else statement in the compute variable menu. Also, assuming this worked, would I have to keep the v4,v5 and salary values in order for the wage values to compute. I ultimately plan on merging this into another spreadsheet and don't wanna have to bring along the faulty variables.

Does this list start or end in Excel?

if money is the last column in a csv file, you can write a perl/refex to remove all commas after the $ (and remove that too, just leave as a number and format it as currency)

I dunno how to program in any language. Would it be too much to ask for the script for this, and how to implement it.

Why doesnt simple Interest functions work for me, what am I doing wrong?

A loan of $14500, will be paid back in 12 monthly installments of $650, and then 12 monthly installments of $900.
What is the annual average rate?

I tried doing the first 12 payments
, but it just returns #NUM

I then tried doing a Net Present Value with an annual discount rate of 4,8%, but the result was $1635,32 ???

What am I doing wrong??

Would you say that you excel at excel?

Is it possible to create a drop down menu that will set the number of decimal places that another cell will display?

If I have a column of 1000+ entries, and I want another cell to display the SUM of the 112 largest entries in that column, what formula should I use?

Sort the column of entries in descending order then use something like =sum(a1:a112)

it could be done with a combination of these two functions...

Rank( number, array, order )
rank the values

SumIf( range, criteria, sum_range )
sum the values *if* the rank is <= 112

Yeah, sorry, I should've added that I didn't want to have to sort.

Thank you, this worked for me.

I also found this online: =SUM(LARGE(TheRange,ROW(INDIRECT ("1:112")))) but I couldn't get it to work for me initally (hence my question here).

I would like to ask 2 questions if I may.

1) When computers made the leap from a monster machine with its own corner office to the desk top many programmers moved into creating custom small business programs. This was great for a while and "programmers with their own business" exploded. Then with the advent of Win95 these programs could basically be purchased off the shelf to do what ever the businesses wished. Do you think your work with excel is basically going back to the 80's and 90's when clients were begging for custom products?

2) I am an older guy who understands what "press any key" means. I have a block to command the computer to do something while my kids pound away with no more concern of breaking the computer or starting a nuclear war than I had using a hammer at their ages. My attempts to use excel are plagued with these fears. Is there a way to organize backups and such so that you can always go back but at the same time not get lost in a sea of backups which I often find myself doing.

Ok so I have a problem with a spreadsheet that I have no idea how to attempt. I have a spreadsheet which contains data in 3 columns and I need to create an array formula that returns the largest n values in one column where the other column satisfy certain criteria. Here is a simplified version of the data:

Status Amount Type
Active 4.166666667 A
Active 20.83333333 B
Active 13.44 A
Active 13.44 B
Active 20.83333333 A
Notice Given 100 B
Active 20.83333333 A
Active 20.83333333 B
Active 153.6 A
Active 0 B
Active 14.16666667 A
Active 0 B
Active 120 A
Active 77.4 B

So what I want is an array formula that will return the largest say 10 Amounts where the Status is 'Active' and the Type is 'B'.

Any help would be massively appreciated!

The Mac version of Excel '11 got some bad reviews on Amazon. Do you have any experience with it? Since I'll probably be running a dual boot system (or Parallels), I'm wondering if I should get the Mac version or just get the PC version of '10.

Apologies to those who had unanswered questions, ive been moving around a lot in the last 3 months and haven't had a chance to keep up to date, if you still have an open question feel free to post it again or PM me and ill have a look.


=MAX(B2:B100*((A2:A100="Active")*(C2:C100="B")))

its an array formula so you need to press Ctrl+Shift+enter after putting it in


I use parallels as well and deleted office for mac long ago. I don't know if anything has changed in '11 but i doubt its significant enough to switch back

edit: saw this was answered already

Assuming cell is A1 and dropdown is A2

right click on the sheet tab and go view code then paste:

Thanks a lot for that. Because I need to show the top n values I amended it by using the Large function instead but I would have been completely lost without your input.

Woops, misread it sorry. Glad i could help

Thanks for your help. I honestly didn't think this was possible. I'm afraid I still haven't been able to make this work. Is there something I need to do to activate this code or something?

I use excel macros extensively at work. I've been working with files that are runlogs, taking data every second. I work with many of these files and want to do many comparisons of different runlogs. I'm starting to think that it would be better if I move my stuff to access... but don't think access is really designed for runlog type data, more listed data. The reason is that I compare maybe 20 runlogs together and the excel sheet becomes hundreds of graphs and calculation sheets after it goes through my macro.

Question is, do you agree that I should explore options in access? Have you ever heard of anyone using runlog data in access successfully?