Replies

URL made into link by HansV by placing before it and after it

Errors in spreadsheets are a big deal. They should certainly be an even bigger deal for accountants. Here’s an article about detecting spreadsheet errors.
http://www.journalofaccountancy.com/Issues…sheetErrors.htm%5B/url%5D

Accountants should also understand how to use Date functions, date math, etc. EOMONTH function should be required so that consistent dates are used.

Even Better! Thanks

Thanks. Exactly what I was looking for. Why doesn’t the help file do a related reference in ceiling? That’s where I started looking. It’s great to have Woody’s Loungers around!

What is the source of the input database? Can you specify the format as yyyy? If not, there’s a way to fix the obvious ones, but the others you’ll never be sure of. Here’s one way to do it. I’m sure there is a more efficient macro that someone else can provide.
Suppose the birthday 09/10/2026 is in column A2. I would make 3 columns – month, day and year in columns B, C, and D. Extract the month in B2 using =month(A2)
Extract the day in C2 using =Day(A2). Extract the year in D2 using =IF(YEAR(A2)>YEAR(TODAY()),YEAR(A2)-100,YEAR(A2)).
Finally in Column E you can have your “good” date: =DATE(D2,B2,C2).
You can do this in one column, but I’ve made it 4 columns for ease of understanding.

I’ve attached a brief example.

Thanks for all the suggestions. I did have Show all windows in the Taskbar checked and Ctrl+F6 works.

I found Next Window and Previous Window in the Quick Access Toolbar. Unfortunately they are commands that don’t have associated icons. That’s one big drawback in 2007 – can’t customize the toolbar icons. Otherwise, I quite like the ribbon and have gotten used to finding most of the commands pretty quickly.

Thanks for all your help!

I’m not clear exactly what you’re asking. Here’s my interpretation – you’d like to put a link in your spreadsheet that will take you to a graph on the web. If that’s what you want, Here’s how to do that.

For Bloomberg – it depends whether you have a dedicated Bloomberg terminal or you’re using their website, you can right click on the graph and copy image location. For example http://www.bloomberg.com/apps/chart?h=152&…l&ticks=SPX:IND%5B/url%5D or
http://images.bloomberg.com/banner/ilba.png%5B/url%5D shows you the S&P or the front page graph respectively. If you are using a dedicated terminal, then you have to use the proprietary API to download data.

I don’t know how reuters is set up, but on their website you can do something similar, however they aren’t formatted quite as well. For example, the S&P chart link doesn’t have labels. See http://www.reuters.com/charts/us_spx122425…-1002072731.gif%5B/url%5D

If you want to have a graph in your spreadsheet that updates with the most current data, that’s a tougher programming problem. You’ll need to be able to download the data and update it automatically. I’ve been trying to do that for a while (not successfully). Here’s my general outline of how I would go about getting an updated graph of the S&P 500. I wouldn’t use Bloomberg or Reuter’s data unless they have something that will automatically download the data for you. (Of course, there are much smarter people than I who could figure out a way to do it.)

1. Use the MSN Stock Quotes Add-in. Download here .
2. Download stock prices or index values that you want.
3. Create a macro to automate the process.

Seems simple, but I haven’t devoted the time or energy to get step 3 finished.

If someone can come up with something, I’d appreciate it.

I did some more digging and was able to answer my own question. Using and changing colors with Excel 2007 seems to be one of the bigger improvements, not just in Excel, but in all of Office 2007. On the Page Layout portion of the ribbon, you can set up any number of themes. You can customize each theme separately. You can set the colors, fonts and effects for each theme. I’m not sure if I’ll use custom effects very often, but it will be nice to have an easy way to change color and fonts.

It’s too bad that when I searched help for “change default color” or variations of that search, here’s what came up:

Change the default font in Excel

Set new formatting defaults for a shape or text box

Vary colors in the same data series

Change the color of gridlines in a worksheet

Demo: Apply your brand to Office documents with themes

Change the default file format for saving workbooks

Set the default printer

Thanks Hans. Your explanation makes a lot more sense than the help files!

Steve – Thank you. Saving as html and reopening didn’t solve the problem, but it did isolate where the issues were. The formatting of several of the sheets was probably corrupted. They came back with a very strange format that I wasn’t able to change. When I removed all the formats and reapplied things seemed to be ok.

Don – I have 2007. The recalculate didn’t work for me either. However, I selected A2 as if to edit and then reentered the formula, it worked. Why? I have no idea.

Thanks Hans and Steve. I was making it much too hard!

I’ve been trying to figure out a similar Countif. However, I don’t always have cells with unique values. For example, I want to count all A’s in a cell. It could be ABC, BA, AC, or A. I never have more than 3 letters in a cell and the A can only appear once. I was going to use a lot of cells to come up with a series of formulas, but if you have a nice, elegant UDF (or other solution) I’d appreciate the help!

CAGR and Rate are not necessarily the same. Rate also assumes that you have different signs.

I don’t know of a function besides the XIRR function that will give CAGR, but you can use this formula:
Assume that the beginning date is in A1 and the ending date is in B1. The values are in A2 and B2.

=((B2/A2)^(365/(B1-A1)))-1

This formula doesn’t work if the numbers change sign, but you only need the beginning and ending value to calculate a CAGR. XIRR works well if you have cash inflows and outflows (sign changes) with irregular dates.

If you have a series of return relatives (1+return), then you can use GEOMEAN function to calculate the CAGR. However, the returns have to have the same periods ( annual, weekly, daily, etc)

I’ve attached an example that shows XIRR and the formula.

The / would activate each of the menus. For example /ESV was paste special values, and /ESR was paste special number formats. Those actually still work in 2007 but you have to remember them with no cues. Before the letter was underlined when you got deep into a menu and didn’t remember the last step.

In 2007 you can activate the menus with ALT, and the keystroke letters for the commands appear. However, the keystrokes are all different now. I’m having trouble seeming the pattern in some of the organization. One command I find irritating is Sort. You can use keystrokes to select the area, open the dialogue box, but you have to use the cursor to select the sort by variable.Some of the other dialogue boxes are similar.

The functionality is probably mostly the same, but the strokes are all different. I don’t like to use the mouse so relearning keystrokes is a pain to me.

I found this comparison http://www.add-ins.com/Excel 2003 versus 2007.htm[/url] that focused on calculation, chart, and file opening speed. I did notice that things seemed a little slower.

I use the conditional average formulas quite a bit, also conditional max, min, stdev and lots of other conditional calculations. So I’ll start using the new ones, but may have to use some array functions for others.

I’m trying to keep and open mind. Just because its different, I don’t want to conclude it’s bad. I am having a hard time doing some simple formatting tasks. I’ve customized the ribbon bar with things I use a lot. In 2003 you can assign a custom icon for toolbar commands that don’t already have an icon. In 2007, you can’t do that. Anything that doesn’t have a predefined icon, you have the same generic icon. Of course, it’s not very useful to have two toolbar commands that two completely different things with 1 icon! I remember what they do by the location in the toolbar.

Suppose that 12/14/2007 is in A1

If you want the date end of the month you can use =eomonth(A1,0).

If you want text that is 12-2007, you need to use the following =month(A1)&”-“&year(a1).