[dabigkahuna]

Since I may be trying to move to a Mac (or Linux), I need to find a spreadsheet that can do the following:


{=SUM(LEN($D7:$BF7)-LEN(SUBSTITUTE($D7:$BF7,BG$3,"")))}


Let me explain how it works (this is from Excel).


When
you enter the formula, you have to press ctrl-shift-enter. This gives
you the curly brackets which you do NOT type in. This process makes
things work as an array.


The formula takes all the contents of
cells in a crow from D7 to BF7. The info in these cells contains
various codes I use for my school classes such as:


A - unexcused absence


a - excused absence


Each cell contains info for one day. That may be nothing or multiple codes (like unexcused tardy and no homework (TH)


It takes the length of the string this creates.


The
formula then takes whatever letter I have in BG3 and takes that long
string and for every instance that it exists in the string, it
substitutes "" (that is, nothing. If the string was 30 characters long
and I was replacing 5 "a" characters, the length of the new string
would be 25. The subtraction of the length of the two strings would be
5. Thus the student had 5 excused absences.


Of course, I have a series of these formulas - one for each code I use.


This is the key thing: it MUST be able to handle upper and lower case letters separately.


So,
what I'm looking for are programs that do this besides Excel (or can do
the same thing some other way as long as I can use the codes I want
separating upper and lower case as well as any number of codes in each
cell). Ideally, I'd like to find this in one of the free programs out
there, but i'd be interested in any that could do it.


Thanks.

[smax013]

While it is not free, there is an Excel version for the Mac and should nominally work the same as the Windoze PeeCee version.

[RastaMon]

Most people aren't aware, but Excel started on the Mac, and was ported to Windows, not the other way around. As Smax stated, from the standpoint of functionality, the two versions are similar. (Caveat: Office 2004 does not run natively on new Macs, since it was written for the PowerPC processor. It will run under Rosetta, which comes preinstalled on new Macs.)

For the Mac, you also have the option to use Numbers, which is Apple's spreadsheet software available in iWork (which also comes with a word processing app called Pages, and a presentation app called Keynote). iWork is significantly less expensive than Office. Office 2008 for the Mac is expected to be released early next year (and will run natively on the Intel hardware).

You also have Open Office.org available on Mac and Linux (and Windows). Mac has a Cocoa port of OOo called NeoOffice. Both OOo and NeoOffice are free.

[dabigkahuna]

Three points. First, while I know Excel for mac is quite similar to that for Windown, there is always the chance that this one specific capability isn't there. I remember when I tried to find a formula that would handle different numbers of codes in a cell as well as handle upper and lower case letters individually. Lots of people told me it couldn't be done. So apparently this is a not widely know solution and when I've looked at some other programs to try to do this with, I couldn't. So the Mac version may do it, but maybe not.

Second, I don't want to buy another version anyway. If I can't find some other solution - preferrably one of the free options out there - I'd probably just prefer to run the Windows version under Parallels or somethnig.

Third, I really would like to get rid of as much stuff from Microsoft as possible!

[smax013]

dabigkahuna said:

Three points. First, while I know Excel for mac is quite similar to that for Windown, there is always the chance that this one specific capability isn't there. I remember when I tried to find a formula that would handle different numbers of codes in a cell as well as handle upper and lower case letters individually. Lots of people told me it couldn't be done. So apparently this is a not widely know solution and when I've looked at some other programs to try to do this with, I couldn't. So the Mac version may do it, but maybe not.

Second, I don't want to buy another version anyway. If I can't find some other solution - preferrably one of the free options out there - I'd probably just prefer to run the Windows version under Parallels or somethnig.

Third, I really would like to get rid of as much stuff from Microsoft as possible!

To my knowledge, the Mac version of Excel has all the same forumulas/functions as the Windoze version. The exception is that Excel 2007 might have added some new functions/formulas. But, I am pretty sure that if it works in Excel 2003 or Excel XP (also known as Excel 2002), then it should work in Excel 2004 for Mac. I will further note that Micro$oft will be releasing Office 2008 for the Mac some time shortly after the first of the year. It will be able to run in Intel native form (which addresses the issue that ~80584] raised) and will likely have any new forumulas/functions that Micro$oft may have added to Office 2007 for Windoze.





And as [~80584] pointed out, there is [Open Office. Open Office has versions on all major platforms. So, you should be able to try the Windoze version now and see if it will do what you want. If it does, then a Mac version/port of it should also then be able to do what you want.



And I will note that if you do get a Mac, it will come with a 30 demo of iWork (which includes Numbers) and Micro$oft Office 2004 for Mac (which contains Excel 2004). Thus, you will be able to try both and see if they will do what you want and if so, then you can go with one of them.

[dabigkahuna]

> You also have Open Office.org available on Mac and Linux (and Windows).
Mac has a Cocoa port of OOo called NeoOffice. Both OOo and NeoOffice
are free. <

I tried Open Office twice, I think - or maybe once was Star Office which I think is much the same. Anyway, the first time, I tried to read my Excel file in it, but the formula didn't transfer properly (the curly brackets ere missing) and the formula didn't work. The second time (newer version), it transferred, but didn't actually work. I don't have a big hard drive so I don't keep these things on my computer if they don't work. That's why I was hoping someone had this or other programs and could try the formulas or could tell me if other formulas that did the same thing would work.

I'm not familiar at all with Cocoa or OOo or NeoOffice.

[dabigkahuna]

Whether I get a Mac or not will be influenced somewhat by whether this can be handled so I'll need to know in advance. But I won't be looking to actually by until Christmas or just after. Like I said, I'd prefer not to have to buy a new version of MS Office (mine is 2000) and I'd also prefer not to have resort to Parallels or Boot Camp.

I've tried Open Office before - those versions didn't handle this but that was a long time ago. I've read a little about Numbers. Not enough detail, but I got the impression it might not be as powerful as Excel. Still, it just needs that one capability to suffice.

[smax013]

dabigkahuna said:

Whether I get a Mac or not will be influenced somewhat by whether this can be handled so I'll need to know in advance. But I won't be looking to actually by until Christmas or just after. Like I said, I'd prefer not to have to buy a new version of MS Office (mine is 2000) and I'd also prefer not to have resort to Parallels or Boot Camp.

I've tried Open Office before - those versions didn't handle this but that was a long time ago. I've read a little about Numbers. Not enough detail, but I got the impression it might not be as powerful as Excel. Still, it just needs that one capability to suffice.

You could always try to find an Apple Store near you and go try it out on a Mac in the store. If I get time, might see if I can verify if it will work in the Mac version of Office.



With regards to Open Office, might still go what you want to do, but just not in the same way (i.e. using the same formula/functions). Thus, you might have to "play around" with it some.

[dabigkahuna]

Well, there is a store I can get too - just a hassle since I have no car (to save money). I may see if I can work out a trip there and take the file on a thumb drive. But I'll still prefer to find a different program (especially one of the free ones so I can get away from microsoft.

> With regards to Open Office, might still go what you want to do, but
just not in the same way (i.e. using the same formula/functions). Thus,
you might have to "play around" with it some. <

Yeah, but I've never heard anyone say if it had any capabilities Excel didn't have. Even with Excel, I had a heck of a time getting a solution for that - someone had to give me the exact formula. I would otherwise never have figured it out!

But your suggestion did give me one idea which may lead to people to come up with a solution.

As I pointed out, the formula basically takes all the codes in all the cells for each day for a student and puts them into a long string which is then compared to another string with all examples of a given code removed. Subtracting the length of one from the other gives the proper result.

But that is done with arrays in one step, which puts it beyond my skills (so someone had to give me the info).

So I was thinking maybe one of these other spreadsheets could do it in two steps. If there is a way to set up a cell (which I'd normally keep hidden) which did nothing more than combine all the codes in those cells, I bet I could figure out how to manipulate that info in more common ways than arrays.

Essentially I'm picturing a use of "Concatenate". But I need to figure out a way to cover a range. I suspect that something like "Concatenate(D7:BF7)" won't work. And using "Concatenate(D7,E7,F7 all the way to BF7)" would exceed character limits for a formula!

I'll look into this with Excel, but perhaps some others can see if at least that can be done in other programs too.

[smax013]

I did confirm that the formula does work in Excel 2004 for the Mac. Thus, if worst comes to worst, you can use the Mac version of Office to do what you want.

I have little doubt that you can do what you want (i.e. count up "a"s or "A"s or "t"s, etc) in the other spreadsheets. It may not be in the same exact way (i.e. that forumula might not work), but I am sure that there are ways to do it.

[dabigkahuna]

That's handy to know for sure - one less thing to wonder about.

> I have little doubt that you can do what you want (i.e. count up "a"s
or "A"s or "t"s, etc) in the other spreadsheets. <

You may be right, but I have lots of doubts based on how hard it was to get this solution. At the time, I was posting about what I wanted to do on a forum with some Excel experts - and they all said it couldn't be done. It wasn't until I told them I had found a way to do it on an Apple IIgs with Appleworks (I had to run a macro which literally moved the cursor to each cell and added the contents to a string which I could then manipulate) that I think they got embarrassed as they started looking seriously for a solution - can't have Appleworks beating Excel, right?

But only this one solution came up and as I said, the testing I have done on other programs didn't work.

I did do some checking using "Concatenate". Seems it won't accept a range and the instruction say you can only reference 30 cells at a time. That isn't enough. Sure, I could create multiple strings and then combine those, but all this would be getting very inelegant!

[dabigkahuna]

I downloaded Open Office and tried it. It works with the present version. Lots of other things to check out though. For eample, in my tests I entered the following text in a cell: "zH". In Excel, it looked fine. In Open Office, it was a bit harder to read. Don't know why, but it is a concern.