Author Topic: EXCEL - there HAS to be an easier way to do this  (Read 4213 times)

Jake

  • Jackass In Charge
  • Posts: 8650
  • Karma: +83/-18
EXCEL - there HAS to be an easier way to do this
« 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:

Code: [Select]
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
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

Perspective

  • badfish
  • Jackass In Charge
  • Posts: 4635
  • Karma: +64/-22
    • http://jeff.bagu.org
Re: EXCEL - there HAS to be an easier way to do this
« Reply #1 on: August 01, 2013, 03:50:24 PM »
For calls you can divide the number of calls by 10 and round down.

Jake

  • Jackass In Charge
  • Posts: 8650
  • Karma: +83/-18
Re: EXCEL - there HAS to be an easier way to do this
« Reply #2 on: August 01, 2013, 04:01:19 PM »
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%)))))
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

Jake

  • Jackass In Charge
  • Posts: 8650
  • Karma: +83/-18
Re: EXCEL - there HAS to be an easier way to do this
« Reply #3 on: August 01, 2013, 04:04:09 PM »
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
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

Perspective

  • badfish
  • Jackass In Charge
  • Posts: 4635
  • Karma: +64/-22
    • http://jeff.bagu.org
Re: EXCEL - there HAS to be an easier way to do this
« Reply #4 on: August 01, 2013, 04:05:42 PM »
Here's mine (untested). Assumes number of calls is >= 0.

=IF(C3>39,4%,ROUNDDOWN(C3/10, 0))

JaWiB

  • definitelys definately no MacGyver
  • Jackass V
  • Posts: 1443
  • Karma: +57/-4
Re: EXCEL - there HAS to be an easier way to do this
« Reply #5 on: August 01, 2013, 06:36:19 PM »
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

JaWiB

  • definitelys definately no MacGyver
  • Jackass V
  • Posts: 1443
  • Karma: +57/-4
Re: EXCEL - there HAS to be an easier way to do this
« Reply #6 on: August 01, 2013, 06:37:44 PM »
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...

Jake

  • Jackass In Charge
  • Posts: 8650
  • Karma: +83/-18
Re: EXCEL - there HAS to be an easier way to do this
« Reply #7 on: August 01, 2013, 10:37:31 PM »
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.
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

JaWiB

  • definitelys definately no MacGyver
  • Jackass V
  • Posts: 1443
  • Karma: +57/-4
Re: EXCEL - there HAS to be an easier way to do this
« Reply #8 on: August 01, 2013, 11:14:01 PM »
What do you mean by self-contained? Are you not distributing a spreadsheet, just the formulas?

Jake

  • Jackass In Charge
  • Posts: 8650
  • Karma: +83/-18
Re: EXCEL - there HAS to be an easier way to do this
« Reply #9 on: August 02, 2013, 02:57:35 PM »
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.
Do not follow where the path may lead. Go instead where there is no path and leave a trail.

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: EXCEL - there HAS to be an easier way to do this
« Reply #10 on: August 02, 2013, 03:04:29 PM »
Create another sheet and put it in there.

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14305
  • Karma: +73/-790
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: EXCEL - there HAS to be an easier way to do this
« Reply #11 on: August 02, 2013, 04:30:42 PM »
Create another sheet and put it in there.
Yeah we have a lot of workbooks that do this.  That's pretty common actually.

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: EXCEL - there HAS to be an easier way to do this
« Reply #12 on: August 02, 2013, 05:30:31 PM »
I'd say easily 90% of my spreadsheets have multiple sheets in them.