I am working on some financial calculations for a start up business. I
need some help getting exactly what I need from excel. So here is the
situation:

I have a table set up on the first sheet. The first column is called
"Sale Amount". In the business there can be sales from 1 cent up to 100
dollars and anywhere in between. So this column begins with $0.01 and
goes all the way to $100.00. The Revenue generated by the sale is then
divided into three different accounts according to a formula. So I have
three more colums labled "account 1", "account 2", and "account 3".
There are formulas in each cell in these colums to calculate the
appropriate money put into each account from each sale.

Now here is where I run into a problem. I want to have another
worksheet with a table that gives me information derived from a given
range of rows from the first worksheet. It will have 2 colums where I
type in a range of cells. Then it will have more columns with formulas
to calculate different values based on the numbers in the account
columns for this range. For instance if I type in "From $.01 To $5.00"
in the first two columns I want formulas in the other rows of this
table to calculate things like the average of all the cells in the
account 1 column in that range. So in this case there would be 500 rows
in this range. Excel would have to search the "Sale amount" row in the
first table to find these rows. Then it would have to add up every
value in the "account 1" column and divide that number by 500. The
number from this calculation would then be posted in the next column in
the second worksheets table right after the "from" and "to" columns.
Excel would have to recognize how many rows total are in the range so
it could divide by that number. It would also have to add only values
in the account 1 column that fell into that range.

I dont know what I would have to do to make this work. Thanks for any
help in advance.


--
wufishmonger
------------------------------------------------------------------------
wufishmonger's Profile: http://www.excelforum.com/member.php...o&userid=35283
View this thread: http://www.excelforum.com/showthread...hreadid=550692

I would be tempted to go for a user defined function in this case which
scans the first sheet for values in your range and keeps a running
total so that averages etc can be worked out.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=550692

would you be willing to explain how I could use data in cells adjacent
to the values that are within the correct range? Or point me to a
tutorial?


--
wufishmonger
------------------------------------------------------------------------
wufishmonger's Profile: http://www.excelforum.com/member.php...o&userid=35283
View this thread: http://www.excelforum.com/showthread...hreadid=550692

Hi!

Sheet1 column A A2:An contain the values 0.01 to n.

Column B = Acct1

Sheet2

A1 = header = From
B1 = header = To

A2 = 0.01
B2 = 0.10

=AVERAGE(INDEX(Sheet1!B2:B21,MATCH(A2,Sheet1!A2:A2 1,0)):INDEX(Sheet1!B2:B21,MATCH(B2,Sheet1!A2:A21,0 )))

Adjust references to suit.

Biff

"wufishmonger"
wrote in message
...

I am working on some financial calculations for a start up business. I
need some help getting exactly what I need from excel. So here is the
situation:

I have a table set up on the first sheet. The first column is called
"Sale Amount". In the business there can be sales from 1 cent up to 100
dollars and anywhere in between. So this column begins with $0.01 and
goes all the way to $100.00. The Revenue generated by the sale is then
divided into three different accounts according to a formula. So I have
three more colums labled "account 1", "account 2", and "account 3".
There are formulas in each cell in these colums to calculate the
appropriate money put into each account from each sale.

Now here is where I run into a problem. I want to have another
worksheet with a table that gives me information derived from a given
range of rows from the first worksheet. It will have 2 colums where I
type in a range of cells. Then it will have more columns with formulas
to calculate different values based on the numbers in the account
columns for this range. For instance if I type in "From $.01 To $5.00"
in the first two columns I want formulas in the other rows of this
table to calculate things like the average of all the cells in the
account 1 column in that range. So in this case there would be 500 rows
in this range. Excel would have to search the "Sale amount" row in the
first table to find these rows. Then it would have to add up every
value in the "account 1" column and divide that number by 500. The
number from this calculation would then be posted in the next column in
the second worksheets table right after the "from" and "to" columns.
Excel would have to recognize how many rows total are in the range so
it could divide by that number. It would also have to add only values
in the account 1 column that fell into that range.

I dont know what I would have to do to make this work. Thanks for any
help in advance.


--
wufishmonger
------------------------------------------------------------------------
wufishmonger's Profile:
http://www.excelforum.com/member.php...o&userid=35283
View this thread: http://www.excelforum.com/showthread...hreadid=550692