formulas

[Stacey]

I am trying to create a formula in excel showing the salary for a client for
a 5 period. The beginning and end salary will be provided. The formula will
need to calculate the annual raise and % of raise that the client received
from point a (beginning salary) to point b (ending salary). I thought I
might have luck calculating a compound annual growth rate (CAGR) utilizing
the XIRR but I am having no luck. If anyone can help with this it would be
very much appreciated.

 

[Duke Carey]

Assume the beginning salary is in A1 and the ending salalry is in A2

=(A2/A1)^.2-1

will give you the compounded annual rate of increase.

 

[Love2pieces]

I need to know if it is possible, and if so, if any one knows how, to take a
number from one cell and distribute among several other cells. I do not want
to do a divide, I don't want it evenly distributed because I can't have
fractions, I need whole numbers. So I just need to take the number 32 (or
whatever) and spread it out as evenly as possible, in whole numbers among 7
seperate cells. Is that possible???

 

[joeu2004]

In the future, start a new thread for a new "topic". Granted, the
Subject chosen by the previous OP was not sufficiently distinct.

I need to know if it is possible, and if so, if any one knows how, to take a
number from one cell and distribute among several other cells. I do not want
to do a divide, I don't want it evenly distributed because I can't have
fractions, I need whole numbers. So I just need to take the number 32 (or
whatever) and spread it out as evenly as possible, in whole numbers among 7
seperate cells. Is that possible???

If the initial number is in A1 and you want to distribute into the 7
cells B1:B7, the easiest way to accomplish that is:

B1:B6: =int($A$1/7)
B7: =A1- sum(B1:B6)

However, that approach is flawed because B7 might be significantly
different from the what you might expect. For example, when A1 is 32,
B1:B6 will be 4, and B7 will be 8(!).

Arguably, a better approach is:

B1: =int(A1/7)
B2: =int(($A$1-sum($B$1:B1)/(row($B$7)-row()+1))
B3:B6: copy B2
B7: =A1 - sum(B1:B6)

When A1 is 32, B1:B3 will be 4, and B4:B7 will be 5.

For a slightly different distribution of 4 and 5 among the cells, use
ROUND(...,0) instead of INT(...). When A1 is 32, B1:B7 contain 4, 5,
5, 5, 4, 5, 4. My only concern with that is: I wonder if there is
some arcane case that could result in a sum of numbers larger than the
original (A1).

 

[Love2pieces]

I'm sorry, I will know better how to utilize this forum next time...

I was so excited that there was an answer, and I really appreciate your help
with this...but...

I put i the formula you gave me and this is what I got. Try as I might, I
could not figure out what wasn't exactly right. I used a blank worksheet,
put 32 in A1 and just copied and pasted your formulas into the appropriate
cells, and this is what it gave me...

A1
32 4 B1
31 B2
25 B3
17 B4
6 B5
-10 B6
-41 B7

Can you be of any further assistance?

 

[Roger Govier]

Hi

There was a small typo in the formula posted by Joue

=INT(($A$1-SUM($B$1:B1)/(ROW($B$7)-ROW()+1))

is missing a closing bracket before the "/" sign

It should read
=INT(($A$1-SUM($B$1:B1))/(ROW($B$7)-ROW()+1))

Excel will have tried to correct it for you and placed an additional
closing bracket at the end, which then gives rise to the results you
show.

 

[Love2pieces]

Yeah!! It worked! Thank you so much...

Hi

There was a small typo in the formula posted by Joue
is missing a closing bracket before the "/" sign

It should read
=INT(($A$1-SUM($B$1:B1))/(ROW($B$7)-ROW()+1))

Excel will have tried to correct it for you and placed an additional
closing bracket at the end, which then gives rise to the results you
show.

 

[joeu2004]

There was a small typo in the formula posted by Joue
is missing a closing bracket before the "/" sign

Thanks for picking up on that. I don't know how that happened, since I
am sure that I cut-and-pasted from a spreadsheet. I suspect I made a
last-minute edit, and I misplaced the cursor before pressing Backspace
:-(.

 

[Roger Govier]

No problem. Happens to me often <g>