Results 1 to 6 of 6

Thread: Excel Function Help?

  1. #1

    Joined
    Oct 2013
    Posts
    186
    Userbars
    4
    Thanks
    201
    Thanked
    112/62
    DL/UL
    5/0
    Mentioned
    48 times
    Time Online
    10d 22h 29m
    Avg. Time Online
    4m

    Excel Function Help?

    My boss asked me to create a win/loss ratio. I used another software to create the report, but he wants to see numbers and percentages

    (you need an account to see links)
    I guess you cant really see in the pic but the columns are PROJECT ID, PROJECT DESCRIPTION, CLIENT, PROPOSAL DATE, STATUS (WON OR LOST), DATE OF WON/LOSS, FINAL DISPOSITION REASON, AWARDED TO


    I would like to figure out a way to show

    1) Total number of won and total number of lost projects
    2) Percentage won / loss (total)
    For EVERYTHING

    and also would like to see that for each individual client if we sort by client. For example,
    say we have 5 projects with Ian Moxon and we won 3 and lost 2, we wanna also see that when we sort

    Is there a way? IF yes, what would be the easiest way without me having to do this manually?

    Thanks guys! I know many of you know a lot about this stuff!

  2. #2
    looklook123123's Avatar
    Joined
    Aug 2014
    Posts
    1,334
    Userbars
    21
    Thanks
    289
    Thanked
    1,033/423
    DL/UL
    27/0
    Mentioned
    242 times
    Time Online
    123d 34m
    Avg. Time Online
    50m
    Quote Originally Posted by Jasmin View Post
    My boss asked me to create a win/loss ratio. I used another software to create the report, but he wants to see numbers and percentages

    (you need an account to see links)
    I guess you cant really see in the pic but the columns are PROJECT ID, PROJECT DESCRIPTION, CLIENT, PROPOSAL DATE, STATUS (WON OR LOST), DATE OF WON/LOSS, FINAL DISPOSITION REASON, AWARDED TO


    I would like to figure out a way to show

    1) Total number of won and total number of lost projects
    2) Percentage won / loss (total)
    For EVERYTHING

    and also would like to see that for each individual client if we sort by client. For example,
    say we have 5 projects with Ian Moxon and we won 3 and lost 2, we wanna also see that when we sort

    Is there a way? IF yes, what would be the easiest way without me having to do this manually?

    Thanks guys! I know many of you know a lot about this stuff!
    Tough to see on my phone but I think I know how to do this. I'll be home and available in a couple hours to help ya out.

    Different but sounds similar to something I did for one of my Co workers on a project last week

    [e]Okay it wasn't just mobile it was difficult to see
    Last edited by looklook123123; 05-04-2017 at 05:35 PM.

  3. #3
    |2eap's Avatar
    Joined
    Jun 2013
    Posts
    3,458
    Userbars
    17
    Thanks
    2,494
    Thanked
    2,680/1,389
    DL/UL
    75/0
    Mentioned
    822 times
    Time Online
    111d 11h 4m
    Avg. Time Online
    40m
    some prob and stat functions for excel will be able to do this. I'm not fluent with excel functions but I can say yes its possible.
    You can search the cell to see if it has a certain phrase (win/loss) to group them into two groups for your total win/loss

    Then break it down from there more specifically.

  4. #4
    Saiyan Race
    j03's Avatar
    Joined
    Dec 2011
    Posts
    13,722
    Userbars
    166
    Thanks
    5,906
    Thanked
    33,077/6,608
    DL/UL
    23/36
    Mentioned
    3,867 times
    Time Online
    563d 5h 25m
    Avg. Time Online
    3h 13m
    Definitely possible. Can you upload your image to postimage.org or somewhere that won't resize your image? And then share the image here. Thanks.
    (you need an account to see links)
    (you need an account to see links)(you need an account to see links)

    ------------------------
    [02/24/2013] Stealth CORE is made into the first standalone Neopets auto-player.
    ------------------------


  5. #5
    looklook123123's Avatar
    Joined
    Aug 2014
    Posts
    1,334
    Userbars
    21
    Thanks
    289
    Thanked
    1,033/423
    DL/UL
    27/0
    Mentioned
    242 times
    Time Online
    123d 34m
    Avg. Time Online
    50m
    In order to calculate total number of win and loss:

    =CONCATENATE(COUNTIF($E$2: $E$6,"Win"),"-",COUNTIF($E$2: $E$6,"Loss"))

    Please note I had to add spaces in here since it was giving smiley icons in the formula. Just take the spaces out in excel obviously.

    There are a few things to note about the above formula:

    $E$2: $E:6: was my test range. You will enter in here whatever range of cells your win/loss are spread across.
    "Win" and "Loss" are whatever you refer to your wins and losses within the highlighted cells. This could be Win, Loss or W, L or whatever they are marked as. The formula is not case sensitive.
    For a percentage just use the above formula by dividing the two numbers. ie 3-2 (W-L) just do =3/2 then make it a percentage in the number tab in the home ribbon. If you want solely a winning and losing percentage against the total, just sum the total jobs and divide the number of wins and losses into it ie 3/5=.6 aka 60% for Wins and 2/5=.4 aka 40% for Losses

    @(you need an account to see links) I think this answers your question. If I missed something, let me know.
    Last edited by looklook123123; 05-04-2017 at 05:50 PM.

  6. #6
    Zachafer's Avatar
    Joined
    Dec 2011
    Posts
    1,235
    Userbars
    11
    Thanks
    769
    Thanked
    1,466/678
    DL/UL
    98/0
    Mentioned
    512 times
    Time Online
    24d 13h 9m
    Avg. Time Online
    8m
    Quote Originally Posted by looklook123123 View Post
    In order to calculate total number of win and loss:




    @(you need an account to see links) I think this answers your question. If I missed something, let me know.
    @(you need an account to see links) if you still need help I am an Excel guru
    @(you need an account to see links) you want to use the [noparse] tag to avoid the smileys. Alternatively you can just disable "auto-parse smileys" from the Advanced reply view. Example :$ :) :D :P

  7. The Following User Says Thank You to Zachafer For This Useful Post:

    looklook123123 (05-04-2017)

Posting Permissions

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