+ Reply to Thread
Results 1 to 14 of 14

Need Help With A < Formula

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Need Help With A < Formula

    1.JPG

    I'm having some trouble getting the current red box as pictured above to calculate correctly. Basically if the inputted data (Y 6.76) is less than 10 I should be in the green. Any suggestions on what type of formula to use so that whenever data is put in that is less than 10 I will get the correct percentage? The current formula in the red box cell is =IF($Y31>0,$Y31/$G31,"100%") Thank you!
    Attached Images Attached Images
    Last edited by Muse83; 07-23-2013 at 02:10 PM. Reason: I made the picture easier to understand

  2. #2
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Need Help With A < Formula

    I updated the main picture at the top so that it's a little easier to understand.

  3. #3
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Help With A < Formula

    Sorry, but this is still unclear. What does 'in the green' mean? What data is less than 10 (Y31, G31, or something else)? Your current formula has no check on whether or not the data is <10, just if Y31 is >0. Do you care about that check?
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,732

    Re: Need Help With A < Formula

    Please post a sample sheet. Pictures are nice to look at, but useless

  5. #5
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Need Help With A < Formula

    If Y31 is 10 or less then H31 should be 100%. Anything above 10 then the percentage in H31 should start decreasing. I'm sorry if I'm not describing it clearly enough.

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Help With A < Formula

    =if(y31<=10,1,y31/g31)

    A sample sheet may help, but I'm not sure if it would since I still don't understand what you want. You need to be more explicit in describing your desired calculations. 'In the green' and 'should start decreasing' are not mathematically descriptive enough.

  7. #7
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Help With A < Formula

    First off, don't quote 100%... simply change it to 1, then format that cell to a percentage.

    If I'm reading it right, can you use this formula?

    =IF($Y31<=10,1,$Y31/$G31)

    If you want the cells to change colors, then use conditional formatting...

  8. #8
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Need Help With A < Formula

    Quote Originally Posted by djapigo View Post
    First off, don't quote 100%... simply change it to 1, then format that cell to a percentage.

    If I'm reading it right, can you use this formula?

    =IF($Y31<=10,1,$Y31/$G31)

    If you want the cells to change colors, then use conditional formatting...
    Thank you, while that does cause H31 to change to 100%, is there a way to make H31 start decreasing when the data inputted is over 10?

  9. #9
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Help With A < Formula

    What exactly do you mean by "start decreasing"? The formula will automatically compute Y31/G31 if what you input in Y31 is less than 10... try inputting different numbers in Y31 and see if that is the result that you want to see...

  10. #10
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Need Help With A < Formula

    For example if I was to put the number 11 in Y31 I don't want H31 to become 110%. Since 11 would be over the 10 minute limit that we are trying to reach each month I would like the overall percentage (H31) to go down after 10.

  11. #11
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Help With A < Formula

    If you use Pauley's or my formula, you won't get 110%... please give it a shot... enter 11 and see what you get...

  12. #12
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Need Help With A < Formula

    is there a way to make H31 start decreasing
    What do you want H3 to look like when the input is 11, or when it's 12, or 13, or...?

  13. #13
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Help With A < Formula

    I can literally think of an infinite amount of 'decreasing' functions as y31 increases. Here are a few:
    =if(y31<=10,1,-y31)
    =if(y31<=10,1,1/log10(y31))
    =if(y31<=10,1,10/y31)

    Do you know what you want? If so, explain it.

  14. #14
    Registered User
    Join Date
    07-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Need Help With A < Formula

    Thanks for your help, it seems to be working now! I apologize again for the lack of detail I provided, today was extremely
    hectic and I wasn't able to spend much time at all with my description. Thank you all again for your help

+ 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: 6
    Last Post: 10-08-2009, 03:45 AM

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