Can any Excel (2003) guru’s help me out with the required formula, please?? I’ve tried to simplify the explanation so you can understand the question.
July deliveries = 85 (in cell B2 of ‘Jul’ worksheet)
August deliveries = 64 (in cell B2 of ‘Aug’ worksheet)
Cell B3 of ‘Aug’ needs to show the percentage increase or decrease compared to last month.
Up to now I have been doing this calculation manually using the mathematical formula as follows:
(%) (divide) (July cell B2) (equals) ‘X’
100 / 85 = 1.176
then……
‘X’ (multiply) (difference between Jul & Aug) (equals) ‘answer’
1.176 * 21 (85 — 64 = 21) = 24.696 %
therefore……
there was a 24.70% difference of deliveries between Jul & Aug, which I can see was ‘less than’ so I manually enter the figure in cell B3 of ‘Aug’ as ‘-24.70%’.
What I need is the correct formula to enter in cell B3 so that the calculation is carried out automatically. The bit that is stumping me is that the result also needs to show whether the answer is a plus or minus figure.
Give me some time in the next couple of days to put my ‘sensible’ head on and I’m sure i can sort it out.
UPDATE!!!
I have so far worked out the following formulas, all of which seem to work;
=(B2/Jul!B2)*100-100
=100*(B2-Jul!B2)/Jul!B2
=(B2-Jul!B2)/Jul!B2 and format the cell as percentage. This is my fav so far.
Krankee, please feel free to look at it some more as I feel there might be a better formula yet!!
ill check at school in the moarning as i dont have excel yet
damm downlodin it for mac is hard
I haven’t forgotten this one.
It’s just that I’m up to my eyes in it at the moment.
I’m having a bit of building work done at present, and I’m doing the labouring. For example, I was on my feet from 0800 this morning. Eventually sat down at 1900.
Thinking about it, you might be best to use the Visual Basic feature.
And this is off the top of my head…
If August July.B2> then August.B2 then
Exit sub
Else August.B2.format = (can’t remember 
)(but it puts it in the negative. )
End Sub
The secret is to devise a formuale that always counts (count)+1. Whether it be days, months, or years.
It turns out to be dead simple
=August!B2/July!B2-1
and as you say, Format the cell as a percentage.
And another one for anyone who dabbles in investments, shares or unit/investment trusts is
=(C13/C14)^(1/((NOW()-C6)/365.25))-1
where C13 is the current value.
C14 is the initial purchase cost.
C6 is the date of purchase.
Which then gives an Annual Percentage Rise/Fall since purchase.
