help with custom formula

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!!

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.

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 :smile:

Suzi said:

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.

Click to expand...

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.