Unexpected output for If formula containing isblank

[JoeB85]

I have a table with these results

F G H I
6 10.5 357 357 -5.68434E-14
7 1 34 34 2.84217E-14
8 1.5 51 51 0
9 0.5 17 17 1.42109E-14

From these formular:

F =IF(SMALL(IF(Profile!$A$2:$M$102=$M$6,ROW(Profile!$A$2:$M$102)-ROW(Profile!$A$2)+1,ROW(Profile!$M$102)+1),1)>102, "", INDEX(Profile!$A$2:$M$102, SMALL(IF(Profile!$A$2:$M$102=$M$6,ROW(Profile!$A$2:$M$102)-ROW(Profile!$A$2)+1,ROW(Profile!$M$102)+1),1),10))&""

G =IF(SMALL(IF(Profile!$A$2:$M$102=$M$6,ROW(Profile!$A$2:$M$102)-ROW(Profile!$A$2)+1,ROW(Profile!$M$102)+1),1)>102, "", INDEX(Profile!$A$2:$M$102, SMALL(IF(Profile!$A$2:$M$102=$M$6,ROW(Profile!$A$2:$M$102)-ROW(Profile!$A$2)+1,ROW(Profile!$M$102)+1),1),11))&""

H =IF(SUM((IF($O$29:$O$52="",FALSE,($O$29:$O$52)))*IF($A$29:$A$52="",FALSE, IF($A$29:$A$52=D6,1,0)))=0, "", (SUM((IF($O$29:$O$52="",FALSE,($O$29:$O$52)))*IF($A$29:$A$52="",FALSE, IF($A$29:$A$52=D6,1,0)))*24))

I =IF(ISBLANK(G6),"big", G6-H6)

WHY is column I giving me such wierd results??!?!?!

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[npamcpp]

Please post a workbook. I've tried to re-construct a sheet to match your formulae, but not with much success. I don't know if the sheet "Profile" is the current sheet or not. Make it easy to help you. Post a file.

[JoeB85]

Oh, sorry! How do I do that?

[JoeB85]

OK, I found out how. So, if you look on sheet 1, Cell I7, I expect the answer to be 0 but it isn't.

[Cutter]

You are getting the #VALUE error because you are trying to do subtraction using 2 text values.
Your formula in that cell is: =IF(ISBLANK(G6),"big", G6-H6)
The ISBLANK(G6) part is FALSE because G6 is not blank. There is a formula in G6 resulting in "". That is a text value having no length and is not the same as blank.
So, because G6 is not blank Excel does the subtraction G6-H6. H6 also has a formula returning "". Excel can't do subtraction using text values.

If you want the results in column I to be 0 then you need (in I6 and down): =IF(G6="",0,G6-H6)
assuming H6 will not be "" if G6 isn't.