How do I group data using defined ranges?

[Guest]

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.

 

[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
(e-mail address removed)

 

[William]

Meant to answer a different post - sorry

--


XL2003
Regards

William
(e-mail address removed)

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
(e-mail address removed)

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.