IF Formula, returning a MROUND formula

dragons

New Member
Joined
Sep 8, 2014
Messages
4
Hi, think i have wasted enough time on this and am going to the experts.

I have a large spread sheet of manhole data.

say column B has the text "round" , "square" etc
and column C has the numbers 1010, 1040, 1180, 1090, 1210 etc...

I wanted to return (in another column, say D) one of two values for all the round manholes, being either 1050 or 1200.

using MROUND(C1,150) i can get 95% of the data to be either 1050 or 1200 (which is good enough for me, for now)

how do i incorporate the IF statement of IF(B1='round')

to put it another way: If B1 equals "round" then C1 is either 1050 or 1200 depending on which it is closer to.

Cheers
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
You did not specify what to do when B is not equal to "round". Below is C1 is returned unmodified in such a situation:

=IF($B1="round",MROUND($C1,150),$C1)
 

dragons

New Member
Joined
Sep 8, 2014
Messages
4
You did not specify what to do when B is not equal to "round". Below is C1 is returned unmodified in such a situation:

=IF($B1="round",MROUND($C1,150),$C1)


Thanks for that, worked a treat.

I'll have to look into the use of '$' for future formulas and how to return a blank if false (not equal to round).

Thanks again
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Thanks for that, worked a treat.

I'll have to look into the use of '$' for future formulas and how to return a blank if false (not equal to round).

Thanks again

The formula would become:

=IF($B1="round",MROUND($C1,150),"")

if you don't want return anything when B1 does not equal "round".
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top