+ Reply to Thread
Results 1 to 3 of 3

Unable to assign values to categories - Rules based formula help required

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    Lincolnshire, UK
    MS-Off Ver
    Excel for Mac 2016
    Posts
    14

    Unable to assign values to categories - Rules based formula help required

    Hi,

    I think I've been looking at this for too long now and can't find my way forwards with it so any help is gratefully received.

    I have 4 licence types with a value representing the sales of each type and I need to assign those sales units to various price bands, of which I have three. The first 450 licences should go into Band A, the next 9550 licences should go into Band B and anything remaining should be assigned to Band C.

    In addition to this, I need to assign the licences to bands in order of licence type priority i.e cheapest (Band A) first.

    I'm trying to avoid VBA where possible but I'm pretty sure it doesn't need it, there's something very simple that I'm just not seeing.

    I've attached a mock up of what I'm looking at and hopefully that will help me better articulate what I'm trying to achieve.

    Thanks in advance.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Unable to assign values to categories - Rules based formula help required

    Hope that I put in values in the correct cells (see attached):

    E11: =MIN(SUM($C$11:$C11)-SUM($D$11:D11)-SUM(E$10:E10),MAX(0,INDEX($D$3:$D$5,COLUMNS($E$9:E$9))-SUM(E$10:E10)-SUM($D$11:D11)))

    and copy across, provided you fill in a large end value in D5 for Band C.

    See if that works,
    berlan
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    Lincolnshire, UK
    MS-Off Ver
    Excel for Mac 2016
    Posts
    14

    Re: Unable to assign values to categories - Rules based formula help required

    Thanks Berlan, that's fantastic and it works like a charm!

    Really appreciate the speedy response too. I feel like you just gave Friday night back to me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] formula required to list cell values based on condition
    By kiranpat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 04:54 AM
  2. Replies: 4
    Last Post: 06-29-2014, 04:33 AM
  3. Complex Excel Formula Required for Obtaining Values based on Column & Row
    By adeel726 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 04:36 PM
  4. [SOLVED] assign values to categories
    By Neico in forum Excel General
    Replies: 3
    Last Post: 10-03-2013, 03:44 PM
  5. Replies: 4
    Last Post: 02-10-2013, 01:44 AM
  6. Replies: 0
    Last Post: 04-22-2012, 06:08 PM
  7. Sum Values Based on Categories in Different Column
    By BScott22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2010, 06:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1