Mobility devices rate plan optimization - looking for a formula

[Calvin1510]

Looking for some recommendation from some very smart people. I’m looking for an excel formula to support the following. I manage a company’s telecom mobile device program. I’m trying to create a formula that will take my users total device usage and find me the lowest rate plan to put those devices in leaving me a 5% buffer so that I don’t go over the allotted data usage pool for each rate plan which would make me incur high overages. Note – Data Pools across devices but not across rate plans

Date Used by each device:
Devices 1 – 88MB | DV2 – 75MB | DV3 – 32 MB | DV4 – 8MB | DV5 – 0MB | DV 6 – 0MB – Total MB used by all devices = 203MB

Available Rate Plans that a device can be put into:
500kB (0.488MB) - $0.65
5MB - $1.99
25MB - $2.50
50MB - $5.00

The goal would be to take the total data set for all devices currently around 100K devices. Run this formula against the monthly device usage and the formula would show which device should go into what rate plan to come up with the lowest cost model but at the same time ensuring that I keep a 5% buffer from going over the allotted data for that Rate plan pool.

Any ideas to get me started or is this even possible in excel.

[tim201110]

VLOOKUP can do it

[Anita Dayoff]

This could be done with VLOOKUP as Tim mentioned. It could also be done with IFS. Here is a pretty simple option that might be a good start.
This is the formula in Column C Rate: =IFS(B2=0,0,B2<E$2,F$2,B2<E$3,F$3,B2<E$4,F$4,B2>E$4,F$5)

Data Plan.JPG