Results 1 to 4 of 4

Thread: help with custom formula

  1. #1

    help with custom formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey everyone. First post so sorry if this is in the wrong place. I'm doing research the field for school so I don't have time to do proper forum analysis. I'm researching radio signals, and need help finding out how to make a custom formula. Right now I am using multiple sheets and rounding equations to log my research for one frequency band. The problem it's, is that I'm now using multiple frequency bands so this won't work. I need to be able to write an equation that will say: if the number is between a and b, rind it to the nearest hundredth. If it's between b and c, round it to the nearest even number in the tenth column. And so on. Different frequency bands have different channel spacing, so that's why I need the equation to identify which band it's in and round it accordingly. Any help would be great.thanx!!

  2. #2
    Neophyte Suzi's Avatar
    Join Date
    Nov 2014
    Location
    Birmingham, UK
    Posts
    2
    Articles
    0
    I am sure there must be a better way of doing it, but it sounds to me like a nested IF function would work - for example:

    if(and(cellref>=a,cellref<=b),round(cellref,1),if(and(cellref>=b,cellref<=c),round(cellref,1),if(etc)

    The formula then looks at the cell reference and if it's between a and b then it will round it. If this is false, then it will move to the next IF statement to check if it's between b and c. In the round section, you can only specify to go to either the appropriate integer or to however many decimal places you need, so for hundreths it would be 2, for tenths 1. I do not know of any way you can specify it to round to the nearest even number in the tenths though; the EVEN function will only do whole numbers, so if you had 9.35 it would round up to 10 and not 9.4.

  3. #3
    Good morning,

    I can clarify a bit if my understanding is correct. You can use the MROUND function:

    =if(and(cellref>=a,cellref<=b),MROUND(cellref,0.01),if(and(cellref>=b,cellref<=c),MROUND(cellref,0.2) .......

    Best of luck and Happy Thanksgiving

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Suzi View Post

    I do not know of any way you can specify it to round to the nearest even number in the tenths though; the EVEN function will only do whole numbers, so if you had 9.35 it would round up to 10 and not 9.4.
    You can use EVEN to give the correct result when working with decimal parts by multiplying out the decimal portion, applying the EVEN function and then dividing to recover the decimal places, e.g.
    = EVEN(A2*10)/10 = 9.4 where A2 evaluates to 9.35.

Posting Permissions

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