Is this possible?

[phat al]

I need to make a sheet that give totals based on monthly figures from
last year.
Then based on difference between the 2 it will show no increase and no
bonus, or it will show an increase and bonus based on increments $75.
to help I will make one that needs work.

One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.

What formula do I use to make this calculation shown under % is nothing
but anything over adds up to a relation with $75. added to a base of
$400 every 10%.
This hurt my head trying to get it right and im new to this more
complicated formulas.

Please in advance to anyone who has time to help Thank you

 

[Arvi Laanemets]

Hi

A1=$400
B1=31.82%
C1=37.18%
D1=A1+INT(MAX(0,C1-B1+0.0999)*10)*75


Arvi Laanemets

 

[phat al]

Ok so lets see if I get this in my head right ….
D1=A1+INT(MAX(0,C1-B1+0.0999)*10)*75

The total showing from that sum is 476.0000 ? I don’t think that’s
right

37.18 - 31.82 = 5.36 , the sum im looking for starts @ $400 and in
increments of ten I add $75 to each one.

0.00% $400
0.10% $475
0.20% $550
and then we jump too where i think we should be
5.20% 4300
5.30% 4375
5.40% 4450

I think the total im looking for is this 5.30% which is 4,375.00, if I
start with a base of $400 and add $75 every 10%?
Am I correct? Or have I made a mistake calculating.
What can I do to that sum to make it work?

Thank you so far for your help it is appreciated

 

[Sandy Mann]

Using Avri's cell location and with B1 & C1 formatted as percentage

=FLOOR(MAX(0,C1-B1),0.001)*1000*75+IF(C1>B1,A1,0)

returns 4375

have assumed that you want a zero bonus if this year's money is less than
last year's

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[phat al]

Thank you bonnie lass for your time and knowledge.
Could you advise me on a book to read that will help me, I was thinking
dummies guide to excel?

 

[Arvi Laanemets]

Hi

I'm somewhat confused - are increments 10%, or 0.1% ???

I understood, that when 37.18% - 31.82% = 5.36% , then it falls into
interval between 0%-10%, which makes the bonus $75. And the formula returns
$475

 

[Sandy Mann]

Thank you bonnie lass

It's bonnie lad actually - but at least you were right with he bonnie part

Could you advise me on a book to read that will help me

You are asking the wrong person because I have never read any books about
Excel - just these Newsgroups and I have never had a computer lesson in my
life.

I have heard in these NG's that John Walkenbach writes good, easy to follow
books, one of which I believe is on Excel Functions but I think that perhaps
you could start by going to your local library and see what is there

--
Good lick,

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[phat al]

Sorry im from Plymouth England and that’s my excuse flower.
So how did you work out the formula if you haven’t read excel?

Good point Arvi I don’t think i don’t think I do explain thing
correctly, I find so much lost over a cold text. I sort of got wha
yours was doing with the 10% and $75 yet fail to see where the su
whent wrong?

Next question is if I have 1% increments and not 10% then what do
change to make the sum ?

100% $400
101% $475
102% $550
102% $625
And so forth , looking at this it seems to be $400 when equal & mor
increments of $75 every percent over, I get the sum by dividing las
years number with this years and multiplying the result by 100 to ge
the total percentage over or so I was told.
So $444,945 / $444,959
Im not sure where the 10% is? To change to 1% or am I missing the bi
picture on your sum
=FLOOR(MAX(0,C1-B1),0.001)*1000*75+IF(C1>B1,A1,0)
Would =FLOOR(MAX(0,C1-B1),0.01)*1000*75+IF(C1>B1,A1,0) this b
correct?

Please help my brain is leaking out of the side of my hea

 

[phat al]

i think this is ok but im not getting a match when the same number whic
i should as it is 100% will be $400
=FLOOR(MAX(0,D8-C8),0.1)*1000*75+IF(D8>C8,A8,0)
gives me nothing .... ah ha haa

i see i dont have A8 with a number in as it is not needed here ....

hel

 

[Arvi Laanemets]

Hi

For 1% increments, my formula will be
D1=A1+INT(MAX(0,C1-B1+0.00999)*100)*75

About your result being different - do you have a formula or value in cell
A1. And when a formula, then what is the exact value returned - format the
cell A1 as general, and look what you got.

 

[phat al]

Ok the total last year was 444,445 and the total this year was 500.000.
The bonus is structured this way ….
100% $400
101% $475
102% $550
103% $625
And so forth
To get to the sum I divide 500,000 with 444,445 and get
1.1249985937517578103027371215786
On the scale above this works out to be 112% or $1300
Do you know how I can achieve this sum?
It needs to give $400 if I match 444,445 and then 1% increments of 75
if I go over last years total

 

[Arvi Laanemets]

Hi

With last year figure (444445) in A1 and current year figure (500000) in B1,
when I at least understood your task:
=400*(B1>A1)+INT(MAX(0,(B1-A1)/A1)*100)*75
or
=400*(B1>=A1)+INT(MAX(0,0.00000000001+(B1-A1)/A1)*100)*75

Formulas differ by behaviour around borderlines. The first formula returns
no bonus, when there is not at least slighest increase, and increases
exactly 1%, 2%, etc. are counted as lower bonus group. The second formula
returns a bonus, when income wasn't decreasing, and increases exactly 1%,
2&, etc. are counted as higher bonus group. You can yourself estimate the
small constant (0.00000000001 in my example) responsible for latter, but be
sure it'll be small enough - otherwise some figures may fall into wrong
(higher) bonus group.

 

[Sandy Mann]

So how did you work out the formula if you haven't read excel?

I learned to program in Microsoft Basic on my daughter's Commodore 64
computer, if you remember them, in the 1970's. That was before PC's and
when 64k of memory was considered big.

At work in the 1980's the company gave the staff a half day, demonstration
only, (no hands on at all), of what spreadsheets were capable of. In the
1990's I was given one of Alan Sugar's Amstrad Word Processors, (which was a
hand-me-down from the Admin Department), and I obtained a disk with a
program called SuperCalc but with no instructions. I learned SuperCalc by
trial and error and what I remembered from the half day demonstration.

In 1999 I at last inherited PC with Excel 95, again as a hand-me-down, this
time from the Finance Department. By now the company were sending staff on
Excel training courses but being as I was able to write spreadsheet better
than those that had done the course it was deemed that I did not need one.

Just out of interest at work I now have an XP machine with Excel 2002

You and Arvi seem to be getting og fime so I will just leave you to it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"

 

[phat al]

Hay that worked Arvi the one that allows 0 to be shown if under last
years.
=400*(B1>A1)+INT(MAX(0,(B1-A1)/A1)*100)*75
Now if I need to change this to higher increments what would I change?
So the increments goes up every time 3% and the increase was $50?
100% $400
103% $450
106% $500
109% $550
112% $600
115% $650
118% $700

 

[Arvi Laanemets]

Hi

Hay that worked Arvi the one that allows 0 to be shown if under last
years.
=400*(B1>A1)+INT(MAX(0,(B1-A1)/A1)*100)*75

=400*(B1>A1)+INT(MAX(0,(B1-A1)/A1)*100/3)*50

In general
=400*(B1>A1)+INT(MAX(0,(B1-A1)/A1)*100/PercentStep)*StepBonus
(for previous formula PercentStep was 1)

 

[Arvi Laanemets]

Hi

I learned to program in Microsoft Basic on my daughter's Commodore 64
computer, if you remember them, in the 1970's. That was before PC's and
when 64k of memory was considered big.

At work in the 1980's the company gave the staff a half day, demonstration
only, (no hands on at all), of what spreadsheets were capable of. In the
1990's I was given one of Alan Sugar's Amstrad Word Processors, (which was
a hand-me-down from the Admin Department), and I obtained a disk with a
program called SuperCalc but with no instructions. I learned SuperCalc by
trial and error and what I remembered from the half day demonstration.

In 1999 I at last inherited PC with Excel 95, again as a hand-me-down,
this time from the Finance Department. By now the company were sending
staff on Excel training courses but being as I was able to write
spreadsheet better than those that had done the course it was deemed that
I did not need one.

Just out of interest at work I now have an XP machine with Excel 2002

It sounds almost like me )

My first experience was at Uni (I studied physik there), where we get a
course of programming: FORTRAN (when I remember rightly), we did write our
programs on paper, which were collected and carried to computer center;
girls there typed them into perforator, then they were feed to computer on
shedule, and after some 2 weeks we did get printouts with results, so we
could look, what went wrong - and start next round.

On my first PC I got my hands on early 80's. I started database programming
with dBase, continued with dBaseIII, FoxBase and FoxPro. At end I tried a
bit of VisualFox too. No courses, and no literature, and no Internet were
available at this time (but program Help was available of-course) so I
simply started with some task, and did find a solution myself. Instead of
Excel, I used QuattroPro, and WordPerfect instead of Word. Plus AutoCad and
some statistic program the name of which I can't remember at moment.

My experience with MS Office was sporadic until 2000, when I started to work
in my current place. As here MS Office is mainly used, and as one of my
tasks is application's support for our users, I had to switch to it. With my
previous experience, it wasn't too difficult. And I discovered the existense
of various MS Office Newsgroups too - which were a great help at start.

 

[phat al]

ok im back from my little holiday, thanks for your help

Ok how do I get this sum do I make a table or is there some cleaver
sum?
I get a number that says say 6.90% and the chart shows that’s $200
How can I make a sum that adds/ convents that to get total??
It will start at 6.80% and go up more but never down that wouldn’t be
entered?

6.80% $100
6.90% $200
7.00% $300
7.10% $400
7.20% $500
7.30% $600
And so forth