#value!

[FRED_SHEET]

#VALUE! Error

I have an Excel spreadsheet in which a random number and a blank is generated for a Numerator for a Fraction in Column F and a Denominator is generated in Column G using a formula based on the number which is generated in Column F.

When I attempt to generate the resulting value for the fraction in Column I. I am getting an error message of #VALUE!.

What is wrong with the formula I am using?

I want 0.00000 to appear in Column I if Column F is blank.

Numerator Random Number Generator – Column F
=IF(RAND()<0.3," ",RANDBETWEEN(1,63))

Denominator Function – Column G
=IF(F9>=32,64,IF(F9>=16,32,IF(F9>=8,16,IF(F9>=4,8,IF(F9>=2,4,IF(F9>=1,2,0))))))

Decimal Equivalent Fraction (Numerator divided by Denominator) Formula – Column I
=IF(F12="",0,F12/G12)

Error Message
#VALUE!

I think the problem has to do with when I an generating a blank entry Column F.

 

[mole999]

if you wrap the formula with
=iferror(,0.0) and custom format the cell with 0.00000 i think

 

[Joyner]

Yes, you are inserting a space " " in the cell so Excel cannot perform the math operation and it will give a "VALUE error. You could wrap the whole formula in =IFERROR(your formula here , what do you want returned if an error)

 

[FRED_SHEET]

Thanks for the response this did the trick.

 

[MARK858]

Or don't put a space in the cell put an empty string i.e.

IF(RAND()<0.3,"",RANDBETWEEN(1,63))

instead of

=IF(RAND()<0.3," ",RANDBETWEEN(1,63))

Excel Workbook
	F	G	H	I
1	2	4		0.50000
2		64		0.00000
3	18	32		0.56250
4		64		0.00000
5		64		0.00000
6	12	16		0.75000
7	17	32		0.53125
8	29	32		0.90625
9	57	64		0.89063
10	56	64		0.87500
11	43	64		0.67188
12		64		0.00000
13	19	32		0.59375
14	1	2		0.50000
15	57	64		0.89063
16	62	64		0.96875
17	10	16		0.62500
18	29	32		0.90625
19	1	2		0.50000
Sheet1

 

[FRED_SHEET]

Or don't put a space in the cell put an empty string i.e.

instead of

Sheet1

	F	G	H	I

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]0.50000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]64[/TD]

[TD="align: right"]0.00000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]0.56250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]64[/TD]

[TD="align: right"]0.00000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]64[/TD]

[TD="align: right"]0.00000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]0.75000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]0.53125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]0.90625[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"]0.89063[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"]0.87500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"]0.67188[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]64[/TD]

[TD="align: right"]0.00000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]0.59375[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]0.50000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"]0.89063[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]64[/TD]

[TD="align: right"]0.96875[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]0.62500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]32[/TD]

[TD="align: right"]0.90625[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]0.50000[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =IF(RAND()<0.3,"",RANDBETWEEN(1,63)) G1 =IF(F1>=32,64,IF(F1>=16,32,IF(F1>=8,16,IF(F1>=4,8,IF(F1>=2,4,IF(F1>=1,2,0)))))) I1 =IF(F1="",0,F1/G1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks I am going to give this a try also, it definitely looks like it works OK. I didn't realize the impact having a space between the two quotations marks has on a formula.​

 

[Peter_SSs]

Here is another set of formulas that you could consider to produce the same results.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

Excel Workbook
	F	G	H	I
1	28	32		0.875
2	12	16		0.75
3	22	32		0.6875
4		64		0
5	29	32		0.90625
6		64		0
7	45	64		0.703125
8	31	32		0.96875
9	59	64		0.921875
10		64		0
11	17	32		0.53125
12	35	64		0.546875
Sample