In Excel, how can I calculate a range for a group of numbers in the spreadsheet? For example, I have the following set of numbers, each in a different cell, but in same row:

5 3 7 9 10 3 6

I want to find the range, which is the difference between the highest and lowest number in the series, in this case 10 - 3 = 7, so the range would be 7. How can I write the formula?

This might be cheating, but I'd do it like this:

1. Use the excel sort function to sort the range in ascending (or descending) order.

2.write a simple formula subtracting the first cell from the last cell in the sorted range.

I can't do a sort because the values must stay in their given columns. I need the entire operation to be a formula that I can paste into other cells.

Thanks anyway crescen7.

Anyone else?

Well I was all set to post a reply because I use this in my day to day work...but then I realized you wanted to know the "Range" and I work with "Ranks". However, my formula for a row (assuming first row used was 1 starting with column a) would be:
=RANK(A1,$A$1:$J$1,0)

I think this will still work for you.

Thanks georgia, unfortunately this doesn't give me the desored result. I'm surprised excel doesn't have a range function. It would look at the largest and smallest of a group and give you the difference. I'm sure this can be done without the function but I'm stuck. I'm pretty good with formulas but this one has got me.

Thanks again.

Anyone else have a suggestion?

Hi Kendor:
See if Excell has the following functions:

=MAX(A1:G1)-MIN(A1:G1)

This is from MS Works but I'll bet Excell has
both the =MAX(range) and =MIN(range)

Hope this helps
Mike b. (AKA Byter)

Dang Byter - I've been working on this all afternoona and was so excited that I figured it out...and you beat me to the punch. However, for excel 2000 the formula is:

=MAXA(A1:G1)-MINA(A1:G1)

And no need to "hope" it works Byter....It does! I've already tested it!

Once again the great folks at the pool come through! Thanks a ton Byter and Georgia!!

I knew it could be done!

Kendor - just explain to my boss why I sat messing with Excel formulas all day instead of working on my traffic reports! LOL

But glad we could help you!