+ Reply to Thread
Results 1 to 4 of 4

How to allocate budget to rank-ordered data?

  1. #1
    Registered User
    Join Date
    12-21-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    4

    How to allocate budget to rank-ordered data?

    I have a table in Excel 2016, which includes the keywords of my paid search campaign.
    I have already ranked the keywords based on their conversion rate (from highest to lowest), and now i need to allocate a total budget of 20K to each one of them, according to their rank.

    Is there a formula to do that ?
    The rank starts from 1 (highest rank) until 35 (lowest rank).

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How to allocate budget to rank-ordered data?

    Hi,
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to allocate budget to rank-ordered data?

    You also need to explain your allocation process. Presumably, everyone gets something (i.e. a base amount) and then on top of that you want to add some top-up which is dependent on the rank position. This could be a linear allocation (e.g. each position gets 100 more than the position below it), but we don't know unless you tell us.

    Pete

  4. #4
    Registered User
    Join Date
    12-21-2020
    Location
    london
    MS-Off Ver
    2016
    Posts
    4

    Re: How to allocate budget to rank-ordered data?

    attached the example

    you can see the format of the file, but in the budget column, i have not added a formula, because i dont know which formula to use.
    the logic is to find a way to allocate the budget automatically, based on the rank. e.g. the budget can be reduced by 500 in each rank.

    the problem with that is: when i have more data, i cannot reduce the budget by 500, because it will be 0 before reaching the lowest rank. any idea for that ?
    Attached Files Attached Files

+ 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. Replies: 1
    Last Post: 01-11-2019, 12:21 AM
  2. Nested IF statement to show under budget, within a % of budget, over budget
    By clafleur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2015, 10:36 AM
  3. Solver or What-If to Allocate Budget
    By catccc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2014, 05:31 PM
  4. Allocate a percentage of score based on rank
    By scopo911 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-11-2013, 08:17 PM
  5. Need to allocate budget on a per month basis vs actual
    By joshgumby87 in forum Excel General
    Replies: 7
    Last Post: 03-11-2013, 08:10 AM
  6. Allocate points according to rank - Tie problem
    By vdgert in forum Excel General
    Replies: 2
    Last Post: 12-08-2011, 09:25 AM
  7. Pulling weekly budget data into monthly budget
    By MarkRabbit in forum Excel General
    Replies: 4
    Last Post: 10-19-2008, 04:28 PM

Tags for this Thread

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