[Rumor]

Anyone have a good resourse for formulas or know a bit about excel formulas? There is something I'm trying to acomplish, but I'm not sure how to do it. I'd try to explain, but without looking at the speadsheet, it probably wouldn't mean much.

[philm]

Err, you can get good tips on Excel with just a normal Google search. Loads of resources out there.

[ghporter]

And even Excel's help is useful. While it doesn't provide volumes of data on each function, it does give you a lot of information.

Say you want to calculate a weighted mean. Open Excel's help and type "weighted mean" in the search box. You get a number of hits on "mean," but you ALSO get a hit on "weighted average" (which is what a weighted arithmetic mean is). It even gives an example with A2 through A4 having prices for three items, and B2 through B4 having units sold. The formula it gives is:=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). It also explains what happens:"Divides the total cost of all three orders by the total number of units ordered (24.66)" That's pretty thorough in my book.

[Shades of Gray]

So, what is your question? Some of us work in Excel every day; I have been writing formulas for 5 years, perhaps I can help. If that doesn't work, I have other links for you.

[larry.cologne]

I also have an Excel question, and am not sure if I should start a separate thread for it, but here goes:

I am a recent switcher to Mac, copying a bunch of old Excel files from a 1997 PC laptop (16 mB RAM, 1.4 gB hard drive) using Windows 98, and must use floppy disks (and an Iomega USB floppy drive for my 2nd-hand Mac PowerPC G4), since I was told that Windows 98 didn't support USB, but Windows 98 did.

After much searching on the web, I found that Windows uses the 1900 date format, and Mac uses the 1904 date format. which means that for each document I am
(1) copying a column of dates to another column,
(2) inserting "1462" (4 years + 1 day) into another column,
(3) concatenating the two columns to a third column,
(4) copying that third column to a fourth column, which I then save as a text file, then
(5) copy that text file back to the original date column, and
(6) format the text to my preferred date format for that column.

This is a rather time-consuming for each Excel file. (I have many small Excel files, each consisting of three to ten worksheets, since I had limited RAM and processing speed). I have not developed any programming skills to speak of, since I did not have internet until recently when I got the Mac, and most manuals I had access to were in German, and manuals in English were astronomical in price.

Is there a quick and easier solution to this? I have not yet found one. Thanks for any suggestions. (I also have to convert Mac Excel files for some PC users).

[philm]

What version of Excel were you using with Windows...? I don't think this should happen as long as the Windows Excel files were generted in Excel 97 onwards.

[Shades of Gray]

As for programming, it wouldn't take much, and you wouldn't need all of those columns (you would if you were doing everything in XL with formulas).

Curiosity, when you say "add 1462", you are referring to adding that to the serial number of the date (which is how XL stores the date). Are you making these conversions on the Mac side? What version of XL on the Mac are you using?

[larry.cologne]

To provide you with further details so as to assist you assisting me, I did the following:

Carefully made notes, and re-tested the dates in Excel files transferred from PC to Mac and back again.

Files were originally composed on the PC (no USB, no modem, non-functioning PCMIA slot, CD-ROM),
broken down into segments a 1.4 MB 3.5" floppy diskette could handle,
copied with Iomega USB floppy drive (couldn't use internet since I had new DSL connection) to Mac,
further manipulated on speedier Mac,
then transferred by floppy disk to PC notebook in order to use my 1998 $1000 HP OfficeJet Pro 1170c combi scanner/photocopier/printer with parallel connection.

Then I became thoroughly confused.
NO LONGER did a date in a date-formatted cell in Excel change to a date 1462 days in the future, or conversely to a date 1462 days in the past when I opened, worked on, and copied the same Excel back and forth between the Mac and the PC.
The copy errors in date formatted cells had occurred frequently from last November to a month or so ago.

It occurred to me that I had many MS-Word daily log documents made on the PC with dates entered in a U.S. English format, such as "May 29th, 2006, Monday,"
followed by text on a following line in a smaller font.
I transferred the documents to the Mac, and continued entering data as "usual."
Instead of using my English version of MS Windows 98 2nd edition MS Office 97 pro Word version 11.1.1,
I was now entering data in the German version of MS Office Word 2004 for Mac.
I would switch between normal page lay-out view to outline view to page lay-out view.
I had used the default Normal templates in MS Word, only adding spelling auto-correct changes.
I presume the errors started because the MS Word program in outline view interpreted the larger text date entries as headings, and the smalller text as body text. There was a conflict between the U.S. formatted dates entered in the document before the switch to the Mac, and the newly-added dates which were by default to be entered as a German-formatted date.
I was entering data while in normal view, in page lay-out view, and also while in outline view. In outline view I would move words or phrases or sentences or blocks of paragraphs up or down the document, and would by-pass date headings and other headers, and blocks of text in tables.
I tried out the document in a trial version of iWorks Pages, then moved it back to MS Word so as to proceed with other, more urgent matters at the time.

The conflicting date formats in the same document probably was the cause of MS Word freezing and me having to force quit the program several times. Perhaps it also caused conlficts in Excel. as of now, today, the date errors are not shwing up. Perhaps they are on holiday. i thank you for your time and effort. You may quit reading here, and i hope I won't need further assistance on this matter.

Before this I had also done a lot of fiddling in the Excel file preference panes, trying to understand the compatibility settings, which I had now to do in German, and which I had difficulty enough with in English (I am on permanent disability early retirement, and all my computer experience is self-taught, from the days of the IBM PC-XT (256 kB RAM?, 10 MB hard drive on the XT, 64 MHz processor?) and using dBase II and dBase III+, and forgettting the little I learned about flow-charts and BASIC in a one-semester 1981 college adult-education evening computer course.

Since I was encounterring errors coming from many directtions, I was learning more but knowing less. The OS X system crashed on me on Day 1, and I used David Pogue's Missing Manuals (Switching to the Mac, Office X for MacIntosh, Panther Edition, and Tiger Edition) to start up in single-user mode, and so on and so forth, and assistance from a long-suffering friend whose techno-English was better than my techno-German or Amt-Deutch.

On MAC
PowerPC G4, with 450 MHz processor 704 MB SDRAM

running
Mac OS X 10.4.6 (Tiger),
using English language,
but with (internaitonal) German defaults for time zone, time, currency and number expressions, keyboard, etc.)

and using
Microsoft Office 2004, with Excel 11.1.1, Word 11.1.1
using German version purchased as is on computer from friend:
(German version has menus, dictionaries, spelling and grammar auto-correct, thesaurus and help in German only.
Settings -- time and date, currency format and number format, can be international).

On PC
1997 166 MHz "Network" brand Multimedia notebook, 16 MB RAM, 1.4 GB hard drive:

running
MS Windows 98 Second Edition 4.10.222 A (U.S. English version, and using German formatting)
and using MS Office 97 Pro (U.S. English version, and using German formatting)


On PC,
I had German settings as default:
time and date:
5/29/2006 5:20 P.M. or May 29, 2006 5:20 P.M. in U.S. formats can be expressed as
29.5.2006 17:20 or 29.05.06 17:20 or 29. Mai 2006 in German formats.

01.01.1904 in cell formatted as text cell format on Mac is expressed as 1462.
01.01.1904 in cell formatted as text cell format on PC is expressed as 1462.

01.01.1900 in cell formatted as date, then re-formatted as text cell format on Mac is expressed as 1.
01.01.1900 in cell formatted as date, then re-formatted as text cell format on PC is expressed as 1.

37404,7222222222 on PC in cell formatted as number or text, then re-formatted custom cell format is 28.5.2002 17:20
37404,7222222222 on Mac in cell formatted as number or text, then re-formatted custom cell format is 28.5.2002 17:20

38866,7222222222 on PC in cell formatted as number or text, then re-formatted custom cell format is 29.5.2006 17:20
38866,7222222222 on Mac in cell formatted as number or text, then re-formatted custom cell format is 29.5.2006 17:20

[Shades of Gray]

Howdy. What setting do you have for the Date on the Windows version of Excel? What setting on the Mac? If you want the Windows setting on the Mac, go to Excel menu (top menu just to left of File) and then select Preferences. On the left click on Calculation. Then on the right uncheck the box for "1904 Date System". Now this will require that you go back in a recalc each instance of a date to get it to work properly.

[larry.cologne]

Thanks, I'll try that.
Saw the 1904 date check-box in the calculate (Berechnung) Excel menu item.
.
I had also been getting error messages about compatibility problems, one or two items below on the Excel menu, and had been fiddling with that.

Might be a day or so before I can go further into this issue. I have a sick male pigeon with possibly inflamed air sacs who is also taking his turn on the nest with fast-growing chicks, physiotherapy, etc. et cetera.

I'll list more details concerning the date formats in my next post.

In the English-version Excel 97 on the PC, I was using Format:Cells:Numbers:CUSTOM to set different German date and time formats for several types of date columns.
In DATE, the default settings were mm/dd/yyyy for 5/30/2006 or mmm/yy for May 06, etc.
.
In CUSTOM I would use tt.mm.jjjj for 30.05.2006, MMM jjjj for Mai 2006, and so on (I'm doing this from memory: I'm not sure about the caps).

In the German-version Excel 2004 on the Mac, which had German settings in DATE as the default options,
I was using Format:Zellen:Benutzerdefiniert (user defined, or custom) to put in dd.mm.yyyy h:mm for 5.30.2006 hours:minutes.
Some documents (for family )required U.. formats, and documents for Germans (income tax, medical insurance, etc.) required German formatting. I have to switch back and forth.

And if I send documents for Germna PC users, maybe I have to first change the date formatting.

The number system is simple. Use a comma for a decimal point, periods or dots (or sometimes a space) for the thousand seperators. And for rounded numbers in prices for window displys and price tags, instead of 2,00 EUR, one can use 2,- (a decimal sign and a hyphen, so one doesn't see too many zeroes in the price). (More stuff you don't need to know).

Also, in my first post, item 3, instead of "concatenate" (joining character strings from sevral cells into one cell), i should have said "sum" or "add."

Thanks, Larry