Combining IF & Round Formulas

[jwelshster]

I am trying to combine a ROUND function with an IF function in the same cell. For example, I first want the formula to round up to the nearest 0.25. Then, IF the value from the cell is less than 1, have it show zero or blank in the cell.

The initial values will be in the G Column and the Formula in the H Column and so forth.

I am building a spreadsheet to use when pricing out different groups/buyers. I only want to have to maintain the initial G Column where the raw numbers will be entered manually. From there, the formula will do the rest. Any help with this would be greatly appreciated as my tiny brain has not been able to figure it out as of yet. All ideas and thoughts are welcome.

Thank you very much!

 

[Eric W]

Welcome to the MrExcel forum!

Maybe:

Book1
	G	H
1
2	0
3	3	3.25
4	0
5	5.5	5.75
6	7.75	8
7	12.75	13
8	13	13.25
9	4.4	4.5
10	4.67	4.75
Sheet19

Cell Formulas
Range	Formula
H2:H10	H2	=IF(G2=0,"",MROUND(G2+0.13,0.25))

 

[jwelshster]

Welcome to the MrExcel forum!

Maybe:

Book1
	G	H
1
2	0
3	3	3.25
4	0
5	5.5	5.75
6	7.75	8
7	12.75	13
8	13	13.25
9	4.4	4.5
10	4.67	4.75
Sheet19

Cell Formulas
Range	Formula
H2:H10	H2	=IF(G2=0,"",MROUND(G2+0.13,0.25))

Pure GENIUS!!!!

Thank you VERY Much! Have a Great Weekend!!!

 

[Eric W]

Happy to help! Thanks for the update. ?

 

[jwelshster]

Ok, So I am trying to use the same formula for the following columns and this is what I am seeing. The formula is working perfectly. I changed the +0.13 to 0.25 because I ultimately want the price intervals to be in 25 cent amounts. Is there a way to manipulate the formula so that "#VALUE!" doesn't show up in the cells that have a zero value? Rather, can we have those cells show "0"? Thank you.

 

[Eric W]

Try this:

Book1
	G	H	I	J
1
2	0
3	3	3.25	3.5	3.75
4	0
5	5.5	5.75	6	6.25
6	7.75	8	8.25	8.5
7	12.75	13	13.25	13.5
8	13	13.25	13.5	13.75
9	13.95	14.25	14.5	14.75
Sheet21

Cell Formulas
Range	Formula
H2:J9	H2	=IF(N(G2)=0,"",MROUND(G2+0.25,0.25))

 

[jwelshster]

Try this:

Book1
	G	H	I	J
1
2	0
3	3	3.25	3.5	3.75
4	0
5	5.5	5.75	6	6.25
6	7.75	8	8.25	8.5
7	12.75	13	13.25	13.5
8	13	13.25	13.5	13.75
9	13.95	14.25	14.5	14.75
Sheet21

Cell Formulas
Range	Formula
H2:J9	H2	=IF(N(G2)=0,"",MROUND(G2+0.25,0.25))

Genius Score - 1,000,000! Thank you very much!! Have a great weekend!