+ Reply to Thread
Results 1 to 7 of 7

Complex calculation using if, and, median

  1. #1
    Registered User
    Join Date
    08-17-2016
    Location
    Sebring, FL
    MS-Off Ver
    2007
    Posts
    6

    Complex calculation using if, and, median

    I'm going to try to write what I am trying to accomplish first in English. I have a formula but something is wrong because it uses the wrong calculation.

    I need a formula to check two different cells. If the numbers in those two cells are between 10 and 48 (inclusive) then it is to perform one calculation, if it is outside those then it should perform a slightly different calculation.

    Here is what I have:

    =IF(AND(F7=MEDIAN(10-48),G7=MEDIAN(10-48)),F5/B8,F5/B8*2)

    But when I have numbers that fall in those ranges it is still giving me the false return which is basically double.

    I hope that make sense and I'm sure I'm just missing something really simple. I've only recently forayed into using more complex formulas.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Complex calculation using if, and, median

    I'm not sure that I'm understanding your use of MEDIAN here. Would the following work?

    =IF(AND(F7<=48,F7>=10,G7>=10,G7<=48),F5/B8,F5/B8*2)

  3. #3
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Complex calculation using if, and, median

    Hi -

    When you use the MEDIAN function, that returns the middle value of the range you specified. NOT any value within that range. In other words, the MEDIAN of 10 to 48 is exactly 29. No other value. So if F7 AND G7 both equal exactly 29, then you will get the true condition. If either is any other number (e.g., F7 = 28 and G7 = 25) you get the false condition.

    You could revise that part of the formula to read:

    =IF(AND(F7>=10,F7<=48,G7>=10,G7<=48),F5/B8,F5/B8*2)

    Does that help?
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  4. #4
    Registered User
    Join Date
    08-17-2016
    Location
    Sebring, FL
    MS-Off Ver
    2007
    Posts
    6

    Re: Complex calculation using if, and, median

    You are awesome, thank you so much! Dr. Google failed me but eventually he led me here.

  5. #5
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Complex calculation using if, and, median

    As an addendum to my earlier post, if the 'Inside' result is supposed to be double the 'Outside' result, then you might be missing some parentheses, as well. Try the following if the efforts above are slightly off:

    =IF(AND(F7<=48,F7>=10,G7>=10,G7<=48),F5/B8,F5/(B8*2))

    EDIT: Nevermind, having re-read it I see that the 'False' return should be the one that's double.
    Last edited by CAntosh; 08-17-2016 at 11:11 AM.

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Complex calculation using if, and, median

    Thanks for the Rep! Glad we could help!

  7. #7
    Registered User
    Join Date
    08-17-2016
    Location
    Sebring, FL
    MS-Off Ver
    2007
    Posts
    6

    Re: Complex calculation using if, and, median

    Thanks again. These helped greatly. It's a ridiculous worksheet that I've used for the past 4 years and had to manually change the formulas when necessary because I never had the time to sit down and work on it in the past. This just made everything a whole lot easier and solved the problem of the wrong quotes being given when I'm out of the office because other's didn't remember the exceptions to the rules.

+ 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: 8
    Last Post: 08-09-2016, 12:50 AM
  2. Excel formula debugging - Median calculation
    By gdaniels in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2014, 08:42 PM
  3. Median Absolute Deviation (MAD) Calculation
    By Arand in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2013, 04:07 PM
  4. Conditoinal median calculation for a series
    By mahershams in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-24-2012, 10:10 AM
  5. Median calculation across two columns
    By captainjazz in forum Excel General
    Replies: 2
    Last Post: 06-21-2011, 10:02 AM
  6. Help with calculation of median
    By sssss in forum Excel General
    Replies: 8
    Last Post: 03-26-2010, 09:43 AM
  7. [SOLVED] How to automate the calculation of the median from a frq distribut
    By Alaska Hydro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2005, 09:10 PM

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