Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
Hi
A1=$400 B1=31.82% C1=37.18% D1=A1+INT(MAX(0,C1-B1+0.0999)*10)*75 Arvi Laanemets "phat al" wrote in message ... 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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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? -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "phat al" wrote in message ... 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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
"phat al" wrote in
message ... Thank you bonnie lass It's bonnie lad actually - but at least you were right with he bonnie part <g 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 with @tiscali.co.uk "phat al" wrote in message ... 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? -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 things correctly, I find so much lost over a cold text. I sort of got what yours was doing with the 10% and $75 yet fail to see where the sum whent wrong? Next question is if I have 1% increments and not 10% then what do I 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 & more increments of $75 every percent over, I get the sum by dividing last years number with this years and multiplying the result by 100 to get 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 big picture on your sum =FLOOR(MAX(0,C1-B1),0.001)*1000*75+IF(C1B1,A1,0) Would =FLOOR(MAX(0,C1-B1),0.01)*1000*75+IF(C1B1,A1,0) this be correct? Please help my brain is leaking out of the side of my head -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
i think this is ok but im not getting a match when the same number which i should as it is 100% will be $400 =FLOOR(MAX(0,D8-C8),0.1)*1000*75+IF(D8C8,A8,0) gives me nothing .... ah ha haa i see i dont have A8 with a number in as it is not needed here .... help -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "phat al" wrote in message ... 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 things correctly, I find so much lost over a cold text. I sort of got what yours was doing with the 10% and $75 yet fail to see where the sum whent wrong? Next question is if I have 1% increments and not 10% then what do I 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 & more increments of $75 every percent over, I get the sum by dividing last years number with this years and multiplying the result by 100 to get 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 big picture on your sum =FLOOR(MAX(0,C1-B1),0.001)*1000*75+IF(C1B1,A1,0) Would =FLOOR(MAX(0,C1-B1),0.01)*1000*75+IF(C1B1,A1,0) this be correct? Please help my brain is leaking out of the side of my head -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
Hi
With last year figure (444445) in A1 and current year figure (500000) in B1, when I at least understood your task: =400*(B1A1)+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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "phat al" wrote in message ... 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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
"phat al" wrote in
message ... 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 with @tiscali.co.uk " |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
Hay that worked Arvi the one that allows 0 to be shown if under last years. =400*(B1A1)+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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
Hi
"phat al" wrote in message ... Hay that worked Arvi the one that allows 0 to be shown if under last years. =400*(B1A1)+INT(MAX(0,(B1-A1)/A1)*100)*75 =400*(B1A1)+INT(MAX(0,(B1-A1)/A1)*100/3)*50 In general =400*(B1A1)+INT(MAX(0,(B1-A1)/A1)*100/PercentStep)*StepBonus (for previous formula PercentStep was 1) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
Hi
"Sandy Mann" wrote in message ... "phat al" wrote in message ... 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 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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible?
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 -- phat al ------------------------------------------------------------------------ phat al's Profile: http://www.excelforum.com/member.php...o&userid=34988 View this thread: http://www.excelforum.com/showthread...hreadid=548169 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|