IF Formula, returning a MROUND formula

dragons

New Member
Joined
Sep 8, 2014
Messages
9
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
9
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,210
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".
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top