+ Reply to Thread
Results 1 to 4 of 4

VBA Help: Show Calculations based upon a numerical entry in a cell

  1. #1
    jjward101
    Guest

    VBA Help: Show Calculations based upon a numerical entry in a cell

    Hi! and Happy New Year. I wish everyone a year filled with health and happiness.

    I have attached a spreadsheet which I am having trouble even finding where to begin working with. In the world of sales, everyone has a quota. If you have a quota of 100k, after the end of a quarter, you know how much you need to sell in order to achieve your quota for the year. What is a little more difficult, is adjusting on the fly. For example, if I know I have a 100k quota, I know that I need to sell 8333.33 per month to meet that goal. But if i have a few bad months, and after one quarter of the year, I only have 5k in sales, i need to adjust that monthly requirement if I am going to meet the 100k quota. That would change the math as i would have to sell 95k, in 9 months, giving me a new goal of 10,555.60 in order to meet my quota.

    On the sheet i have attached, i have the proper control data, and have a simple question of "How many Quarters Remain In This Year?" If I enter "2", then I would like the results beneath to only show Quarters 3 and 4. If I enter 3, then i'd like the results to indicate quarters 2,3,4. I would also like to have the option of breaking this down monthly, but will be happy if I can get it to work quarterly only.

    Any help would be greatly appreciated. In the covid era, everything is more micromanaged and it has gotten more difficult to keep food on the plate. Being able to make this work will be all the difference in the world as far as planning and succeeding.

    Thanks Folks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: VBA Help: Show Calculations based upon a numerical entry in a cell

    Perhaps
    G8=IF($AB$4<4;"";$P$4/$AB$4)
    M8=IF($AB$4<3;"";$P$4/$AB$4)
    S8=IF($AB$4<2;"";$P$4/$AB$4)
    Y8=IF($AB$4<1;"";$P$4/$AB$4)
    Attached Files Attached Files
    - Battle without fear gives no glory - Just try

  3. #3
    jjward101
    Guest

    Re: VBA Help: Show Calculations based upon a numerical entry in a cell

    Thanks PCI! I have been able to make this work, but am having trouble with one issue. Perhaps you could assist?

    I have copied that table so as to simulate over achieving by 10%, 25%, and 50%.
    In doing so, I am unable to make the following formula work with error check.

    The formula: =IF($AB$4<4,"",$P$4/$AB$4)*1.1
    (I added *1.1 on the end of what you gave me to add the 10%)

    I am basically trying to have this function so that i do not see the #Value! Error, by changing the value to 0m then using conditional formatting to change the color of the text so that each column with no applicable data is just blank, not showing errors.

    I was able to achieve the same with other forumulas, using -If(G2/A8,0) but that is not seeming to work here with the more complex formula. Do you have any suggesstions as to what might work for me?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: VBA Help: Show Calculations based upon a numerical entry in a cell

    See
    =IF($AB$4<4;"";$P$4/$AB$4*1,1)
    Attached Files Attached Files
    Last edited by PCI; 01-03-2022 at 11:09 AM.

+ 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: 3
    Last Post: 08-05-2020, 10:33 PM
  2. Replies: 4
    Last Post: 06-10-2019, 12:46 AM
  3. How to stop formula/calculations based on a text entry
    By jamesproudlock1993 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2018, 10:25 AM
  4. [SOLVED] HELP - Based on "C" cell entry, Find mismatch figure in row. And save/show separately
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2014, 04:23 AM
  5. Can I format a numerical cell to show as textual?
    By skiwoods in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2013, 10:32 PM
  6. Replies: 1
    Last Post: 03-22-2012, 01:28 PM
  7. Hide/Show a row based on data entry in another row
    By ivory_kitten in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-17-2006, 07:15 AM

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