Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a excel sheet where i need to calculate based on numbers in a slab, for instance:

  1. 1-10 users @ $1
  2. 11-20 users @ $0.8
  3. 21-200 users @ $0.5
  4. 201 users or more @ $0.3

I have a feeling this has something to do with modulo arithmetic but haven't got a clue where to start in Excel.

So far I have tried calculating the users per block as per the spreadsheet view:

slabs

For the example above, with 60 users, there would be 10 users in the first slab (1-10 users), 10 users in the second slab (11-20 users), and 40 users in the third slab (21 to 200 users).

The total cost would then become:

[ $1 * slab 1 user count (10 users) ] + [ $0.8 * slab 2 user count (10 users) ] + [$0.5 * slab 3 user count (40 users) ] = $38

Can anyone please advise on the best way forward.

share|improve this question
    
Can you please explain a little better? Tell us what you've tried already. Also provide an example of how your data is arranged in your spreadsheet and how you expect the results to be. –  CharlieRB Apr 23 '13 at 11:10
    
I have expanded on the question, so far I can calculate the slab width, but I need to work out the users in the slab - this is where i am stuck. –  g18c Apr 23 '13 at 11:30
    
What do you mean by slab? Also, how does cost figure into it? –  dav Apr 23 '13 at 11:39
    
How do you calculate "users in slab"? Is this derived from the "slab width"? –  w3d Apr 23 '13 at 11:41
    
Users in slab is (the maximum of (the minimum of User count - Slab Start + 1 and slab width) and 0). No Excel available right now, but this should be easy. Screenshot of the spreadsheet in Apple Numbers. From Users in slab, you get Slab Cost, sum these up and you're done. Note that the last slab has end and width only to allow me to use the same formula. –  Daniel Beck May 1 '13 at 9:53

2 Answers 2

up vote 1 down vote accepted

I'd follow one of two approaches. 1) macro/vba programming 2)VLookups and add some extra data to your sheet.

The macro would:

1) Loop through the rows in the 'Slab End' column to find the 'slab' number that User Count fits in by locating where Slab Start > User count.

In your example this would be the slab 21 - 200.

2) Work out the amount for the 'slab steps' (if that makes sense), by multiplying the Slab Width for every slab lower than the slab found in step 1) and adding the results

In your example this would be (10 * 0.80) + (10 * 1.00)

3) Subtract the widths from the User Count to find the number that are left for the slab found in step 1).

In your example this would be 60 - 20 = 40 * 0.5.

However, I think adding some extra data to your sheet and using VLookup would work without resorting to macros. I would add columns of 'CountLessThanSlab' and 'CostOfCount' to each row resulting in a sheet that looks like:

Start    End     Width     CountLessThan    CostLessThan    SlabUnit
    1     10        10                                          1.00
   11     20        10                10           10.00        0.80
   21    200       180                20           18.00        0.50
  201    500       300               200          108.00        0.30

Then your formula would take the User count (60), use VLookup to find the correct row (21, 200, 180, ...), subtract 'CountLessThan' (20) from User count to get 40, and add the sum of that figure multiplied by the Slab Unit cost to the AmountLessThan in the same row. Something like this should do it:

=vlookup(B1, $B$3:$E$7, 4, true) + ((B1 - vlookup(B1, $B$3:$E$7, 3, true)) * vlookup(B1, $B$3:$E$7, 5, true))

Caveat: I don't have excel in front of me, atm.

Edit: I'm a drongo

The concept is valid, but the formula is wrong. (I incorrectly remembered the looked up value being less than the relevant value in the table, but it's the other way around.)

This formula works, using the table setup I mentioned above:

=VLOOKUP(B1, $A$3:$F$7, 5, TRUE) + ((B1 - VLOOKUP(B1, $A$3:$F$7, 4, TRUE)) * VLOOKUP(B1, $A$3:$F$7, 6, TRUE))
share|improve this answer
    
Ach! started off including Col A, corrected that to B, but didn't update formula. Table/array should be B3:F7. Will double check & update formula at work tomorrow. –  mcalex Apr 29 '13 at 18:50
    
PS: What do you get? - could be a bracket error –  mcalex Apr 29 '13 at 18:57
    
At work in about an hour, but they should be 18 + ((60-20) * 0.5) –  mcalex Apr 30 '13 at 0:31
    
OK. Edited and now correct. –  mcalex May 1 '13 at 5:58
    
Drongo? - or altruist? IMO it is a good concept and was a damn good first effort without Excel in front of you. I have considered an upvote but prefer nested IFs here (despite a general inclination to avoid these) because there is more of a pattern there, the formula is shorter and fewer helper columns are used. –  pnuts May 1 '13 at 9:22

To assist simplification and minimise further entries, add unit prices in ColumnE add cumulative costs in ColumnF and apply formula as below:

=IF(B1>B6,F6+E7*(B1-B6),IF(B1>B5,F5+E6*(B1-B5),IF(B1>B4,F4+E5*(B1-B4),B1*E4)))

SU586492 example

Edit:

Understanding the nested IFs might be a little easier with B1 a named range (Users), the contents of E shunted up one row and the formula layout changed:

=IF(Users>B6,F6+E6*(Users-B6),
 IF(Users>B5,F5+E5*(Users-B5),
 IF(Users>B4,F4+E4*(Users-B4),
                E3*(Users))))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.