1. B

    Sumproduct Tiered % Bud Achieved Bonus Twist

    Dear All, I'm struggling with Sumproduct formula to calculate bonus based on percentage budget achieved (see below). My desired amount Column U and the formula I'm trying to implement in Column T. If % Budget achieved is 100% then you expect 7.5% Bonus, $7,500 (100,000*7.5%). If you achieved...
  2. V

    Exclude some records from a SUMPRODUCT ranking formula

    Hi, I have a table of SalesReps with a two ranking formulas. The resulting rankings are used on dashboards on other worksheets. The formulas rank the performance of SalesReps within each RepGroup in ASC and DESC order (so that we can report on the top performing and bottom performing reps)...
  3. T

    Counting data

    Assume I have this column of data: a b c d e a b If I type in cell D1: =COUNTIF(A:A,A1) and copy the formula down, I get: 2 2 1 1 1 1 1 which is correct. But what I am seeking is: 1 of 2 ' because it's the 1st a of 2 1 of 2 ' because it's the 1st b of 2 1 of 1 ' because...
  4. D

    Conditional Formatting Marker Line Chart

    Hello all, I have a table with two types of percentages based on a time period: Predicted Achieved I plotted the graph, but I wondered if it is possible to conditionally format the markers of the Predicted series. Eg: At week 41 I predicted 2.5% and achieved 2.5%. Green marker. In week...
  5. F

    Show target missed for last how many months

    Hi, I have a KPI dashboard where I have data from 2016 onwards. Each KPI data is organized in each row. KPI name is placed in A column and it's target in B column followed by monthly achievements. I would like to know for how many consecutive previous months the target is not achieved for...
  6. C

    Find date from target %

    Hi, I have a set of values for dates and corresponding %s 1-Jan-18 8% 1-Feb-18 6% 1-Mar-18 8% I need a single cell formula to find the particular date at which a target cumulative % is achieved For Eg, the target cumulative % of 20% will be achieved on 22-Feb-18
  7. H

    Using an image based on query result

    Hi All Is it possible to subtract 2 cells to se if the figure increases, drops or remains the same (this part I can do) however I would like the result to show an image, either an arrow up or down or - mark. How can this be achieved. Thanks
  8. B

    YTD on multiple columns

    <colgroup><col span="24"><col><col><col><col></colgroup><tbody> I need to some help to sum the coloums from month to calulcate the Year to date, the problem is I have two type of columnes I need to sum </tbody> <colgroup><col...
  9. E

    month of achievement formula

    Hello all, Please if anyone can help me with this topic. I have an agreement set as a value to be achieved , and billed revenue for each month for a 24 month timing. I have also s start and end date of the agreement. I have found if the target has been achieved or not but cannot identify in...
  10. hrayani

    Conditional Format

    Hi, I am preparing a textile worksheet where i have to enter the required and achieved bed sheet sizes. For Example Cell A1 = 140 x 200 ( This is the required bed sheet size ) Width x Length Cell A2 = 141 x 200 ( This is the achieved bed sheet size) Width x Length the achieved size differs...
  11. O

    Rank with criteria

    Hey, guys, I'm really going crazy with this as I have not been able to figure it out for a couple of days. Let's take the following picture as an example: I'm basically looking for a formula on input on column D which does the exact same thing but with one exception. Let's say we on row 21 we...
  12. M

    Designation & % of Achievement

    Hi friends, I have a Sales Incentive Program that payouts to staff based on their designation and % achieved. Below is the criteria table. <tbody> Designation Pay-1 90% Pay-2 100% Pay-3 110% Pay-4 120% Sales Associate 60 100 150 300 Watches Technician 60 100 150 300 Senior Sales...
  13. V

    Help on formula

    Hi All, I don't understand what goes wrong with this formula.. <colgroup><col><col><col></colgroup><tbody> Col AS2 Col AT2 Col AU2 Renewal 68:43:42 formula Col AS3 Col AT3 Col AU3 Renewal 36:17:57 formula </tbody> formula is...
  14. S

    inserting 24 hr time anytime

    I require 24 hour time to be formatted in cell D11. What happens is the actual time may not get entered until later on Example: The current time is say 13:30 but i need to enter the actual time achieved which was 10:30 in D11 What is the easiest way to achieve as i want 24 hour displayed...
  15. V

    Protect cell from editing based on another cells

    Dear Team, I have an excel sheet which is filled by the employees. Range "B" and "C" have data validation as list set to Yes/ No. If "B" and "C" are marked as "NO", Range "D:F" should be protected from editing. The event should be triggered on Cell value change. Can this be achieved?
  16. avisoft20

    Formula for condition.

    Dear expert, I am working on a table but i am fetching some issue.Table given below A B C F <tbody> Column CAT TGT DIF Condition 1 SPL 1 2 CAT-1 2 Achieved 3 CAT-2 2 Not Achieved 4 CAT-3 2 5 CAT-4 3 6...
  17. W

    Commision Calc

    I'm trying to create a Commission Calculator. The plan is 0 commissions is paid until 90% of target achieved, when 90% achieved 1% is paid on all sales, 95 - 100 is 1.5%, 1000 - 105% is paid at 2% and above 105% we pay 2.5%
  18. W

    Commision Calculator

    I have posted a similar thread earlier but the formaula I am looking for is now different. I'm looking to create a formula that calculates sales commission from monthly sales achieved. Cell A1 will contain monthly sales achieved. The commission structure is $0-$14,999 - 10% 15k-18k - 20%...
  19. W

    Commision Formula

    I'm looking to create a formula that calculates sales commission from monthly sales achieved. Cell A1 will contain monthly sales achieved. The commission structure is o 0-2000,0% o 2001-4000,15% (retroactive to dollar 1) o 4001-6000,30% (30% applies to 4001-6000) o 6001+,45% (45%...
  20. A

    School Spreadsheet

    Here is Sheet 1. This is a student grade book. If a student has an x under a topic, that means they have achieved that goal. Test has therefore achieved 'Recognise the use of binary numbers...' etc. Here is sheet 2. When a student types their name: 'Test' in this example -> I want the...

Some videos you may like

This Week's Hot Topics