EntropySink
Technical & Scientific => Programming => Topic started by: Jake on August 01, 2013, 03:44:32 PM
-
I am putting together a standardized way of increasing maintenance contracts. The increase depends on two factors: Age of equipment and amount of calls placed. I want to do this in excel so that we can use it company wise. Here is the scheme:
1-2 years old – 0% increase
3-4 years old – 1% increase
5-7 years old – 2% increase
8-10 years old – 3% increase
11+ years old – case by case basis
0-9 calls – 0% increase
10-19 calls – 1% increase
20-29 calls – 2% increase
30-39 calls – 3% increase
I have played around with this for a few minutes, and have the age figured out with this formula:
=IF(OR(B3=1,B3=2),0%,IF(OR(B3=3,B3=4),1%,IF(OR(B3=5,B3=6,B3=7),2%,IF(OR(B3=8,B3=9,B3=10),3%,20%))))
So basically a shit load of logical statements. And I can do the same thing for the amount of calls. But it just seems there should be a much simpler way of doing this in excel. I have an urge to write some C++ !
If any of you much smarter people can assist, I would appreciate it
-
For calls you can divide the number of calls by 10 and round down.
-
This is what I did for calls:
=IF(C3<=9,0%,IF(AND(C3>9,C3<=19),1%,IF(AND(C3>19,C3<=29),2%,IF(AND(C3>30,C3<=39),3%,IF(C3>39,4%,20%)))))
-
the complete statement to get this within one cell is:
=SUM(IF(OR(B3=1,B3=2),0%,IF(OR(B3=3,B3=4),1%,IF(OR(B3=5,B3=6,B3=7),2%,IF(OR(B3=8,B3=9,B3=10),3%,20%)))),IF(C3<=9,0%,IF(AND(C3>9,C3<=19),1%,IF(AND(C3>19,C3<=29),2%,IF(AND(C3>30,C3<=39),3%,IF(C3>39,4%,20%))))))
it works fine. Just seems so freakin messy
-
Here's mine (untested). Assumes number of calls is >= 0.
=IF(C3>39,4%,ROUNDDOWN(C3/10, 0))
-
For the first one, VLOOKUP (or HLOOKUP). Create a column containing age (0-11) and another column next to it containing the %'s. Then VLOOKUP([Age cell],[lookup columns],2) will give you the percentage
-
Also, it looks like if the value is not in the table (say you enter 12 years), it returns the last entry. So if you put "Case by case" in the last row of the second column, then you'll get "Case by case" for anything that doesn't fall in the 0-10 range. Of course I might have a different version of excel than you...
-
Here's mine (untested). Assumes number of calls is >= 0.
=IF(C3>39,4%,ROUNDDOWN(C3/10, 0))
That is pretty clever Perspective, and it almost works. returns, 0,1,2 or 3 for <=39 but .04 for > 39. It is a nice solutions, but if I decide to change my bounds, i.e. 1-5 0 increase, it would not work. I like it though.
Jawib, I wanted it to be self contained. But good suggestion, I was not aware of those functions.
-
What do you mean by self-contained? Are you not distributing a spreadsheet, just the formulas?
-
What do you mean by self-contained? Are you not distributing a spreadsheet, just the formulas?
I am, but if I understand your example I would need to create a table with the values.
-
Create another sheet and put it in there.
-
Create another sheet and put it in there.
Yeah we have a lot of workbooks that do this. That's pretty common actually.
-
I'd say easily 90% of my spreadsheets have multiple sheets in them.