How do I group data using defined ranges?

Y

Yenabi

I would like advice as to how I can group data together using my own defined
ranges. I want to form a graph that will allow me to represent the number of
data points which fall into certain ranges of intensity. I would like to
have the number of objects which fall in the range to be on the x-axis and
the range scale to be on the y-axis. I cannot figure out how to do this
using an excel function rather than doing the calculations manually. I'm
using Microsoft Office 2003 on Windows XP.
 
W

William

Hi Yenabi

1) I am assuming your current net income cells are in the range B8:F8
2) Enter (without inverted commas) "1" in cell H14
3) Enter the formula =B$8 in cell B14
4) Enter the formula =C$8*$H14 in cell C14
5) Copy cell C14 to D14:H14
6) Enter the formula =IRR(B14:F14) in cell A14

Once you have set this up....
1) Select "Tools>Goal Seek"
2) In the "Set Cell" box enter (without inverteed commas) "A14"
3) In the "To value" box enter the IRR you wish to achieve, say, 0.04 (which
is 4%)
4) In the "By Changing cell" box enter (without inverteed commas) "H14"
5) Click OK.

[You could then copy the range A14:H14 to, say, A15:H20, enter a range of
desired IRRs in cells A15:A20 and repeat the above "goal seek" process for
each row thereby constructing a table showing different cash flows for
different IRRs.]

--


XL2003
Regards

William
[email protected]
 
W

William

Meant to answer a different post - sorry

--


XL2003
Regards

William
[email protected]


William said:
Hi Yenabi

1) I am assuming your current net income cells are in the range B8:F8
2) Enter (without inverted commas) "1" in cell H14
3) Enter the formula =B$8 in cell B14
4) Enter the formula =C$8*$H14 in cell C14
5) Copy cell C14 to D14:H14
6) Enter the formula =IRR(B14:F14) in cell A14

Once you have set this up....
1) Select "Tools>Goal Seek"
2) In the "Set Cell" box enter (without inverteed commas) "A14"
3) In the "To value" box enter the IRR you wish to achieve, say, 0.04
(which is 4%)
4) In the "By Changing cell" box enter (without inverteed commas) "H14"
5) Click OK.

[You could then copy the range A14:H14 to, say, A15:H20, enter a range of
desired IRRs in cells A15:A20 and repeat the above "goal seek" process for
each row thereby constructing a table showing different cash flows for
different IRRs.]

--


XL2003
Regards

William
[email protected]


Yenabi said:
I would like advice as to how I can group data together using my own
defined
ranges. I want to form a graph that will allow me to represent the
number of
data points which fall into certain ranges of intensity. I would like to
have the number of objects which fall in the range to be on the x-axis
and
the range scale to be on the y-axis. I cannot figure out how to do this
using an excel function rather than doing the calculations manually. I'm
using Microsoft Office 2003 on Windows XP.
Click to expand...
Click to expand...
 
Top