Back with another Excel IF problem

00Seven · 06-03-2005, 11:15 AM

How can I get this formula to leave blank if there is no value in H9?

=IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,"")))

When there is no value in E9, F9 and G9, it still pulls the value of E$8

Thanks

Chevy · 06-04-2005, 05:07 PM

Quote:

When there is no value in E9, F9 and G9, it still pulls the value of E$

What is H9 equal to in this case? If all are blank, then the first true condition will be carried out.

**ReeKorl** · 06-06-2005, 03:54 AM

Try this:

Code:

=IF(AND(ISBLANK(E9),ISBLANK(F9),ISBLANK(G9)),"",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))))

Blue bold writing shows the additions. What it does is checks to see if all three are blank, if so puts a blank, otherwise does the original formula.

00Seven · 06-06-2005, 06:46 AM

H9 is this formula that selects the lowest value of E9, F9 and G9

=IF(ISERROR(ROUND(ls3(E9,F9,G9),2)),"",ROUND(ls3(E9,F9,G9),2))

This did not work:

=IF(AND(ISBLANK(E9),ISBLANK(F9),ISBLANK(G9)),"",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))))

**ReeKorl** · 06-06-2005, 07:00 AM

Hmm... should have worked. Can you post the file again (sorry - I deleted the previous old versions) and I'll see what should be there.

00Seven · 06-06-2005, 07:51 AM

This formula seems to work:

=IF(H9="","",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))))

**ReeKorl** · 06-06-2005, 08:49 AM

Arg, I'm thinking far too complexly again.

Yeah, that should sort out the problem quite easily. Nice one.

00Seven · 06-06-2005, 08:57 AM

ReeKorl....

The Whold Project works great now, but I have a slight problem where some figures after lines 85 are not dropping in even though the formula is ok. Then after a another section, no figures are inputted.

I have changed this far from the test one and do not realy want to put it on the site for all to see, is there a way I can send it to you to look at?

**ReeKorl** · 06-06-2005, 10:20 AM

I have PM'd you my email address. Send it there and I'll have a look at it for you.

**ReeKorl** · 06-07-2005, 02:42 AM

OK, two updates here. Some of the lines aren't dropping in because vendor1's prices sheet had blanks in the cost section - put them in as 0's instead and it works.

Next, in some cases (for some strange reason) the VLookup brings back values slightly different to their true values from the prices sheet. It's probably the decimal->binary->decimal conversion again. This is stopping the correct lowest vendor text being produced. In columns E F and G on the Inventory page, you'll need to put this in:

Code:

=round(original formula goes here,2)

This will round the values back to the nearest cent and allow the checker to notice they are actually equal. It's highly unlikely the error will ever be more than half a cent, but if you do find it happens, you can use the rounddown function instead.

00Seven · 06-07-2005, 06:26 AM

I am a dummy, I did not see I had missed putting them in.

I changed the array numbers to increase the length of the sheet, but the program stops putting data into the Inventory sheet at line 171 or item code 10164.

00Seven · 06-07-2005, 11:46 AM

Never mind I think I got it. I was thinking in the wrong section, it was not the VB, it was an ajustmetn in the sheets and formula.

Gosh I must be a pain in the but sometimes.

**ReeKorl** · 06-07-2005, 12:08 PM

Not at all, I'm glad to help

Glad you got it working.

06-03-2005, 11:15 AM	#1 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Back with another Excel IF problem How can I get this formula to leave blank if there is no value in H9? =IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))) When there is no value in E9, F9 and G9, it still pulls the value of E$8 Thanks

06-06-2005, 03:54 AM	#3 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Try this: Code: =IF(AND(ISBLANK(E9),ISBLANK(F9),ISBLANK(G9)),"",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,"")))) Blue bold writing shows the additions. What it does is checks to see if all three are blank, if so puts a blank, otherwise does the original formula. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-06-2005, 07:00 AM	#5 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Hmm... should have worked. Can you post the file again (sorry - I deleted the previous old versions) and I'll see what should be there. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-06-2005, 08:49 AM	#7 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Arg, I'm thinking far too complexly again. Yeah, that should sort out the problem quite easily. Nice one. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-06-2005, 10:20 AM	#9 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	I have PM'd you my email address. Send it there and I'll have a look at it for you. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-06-2005, 06:46 AM	#4 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	H9 is this formula that selects the lowest value of E9, F9 and G9 =IF(ISERROR(ROUND(ls3(E9,F9,G9),2)),"",ROUND(ls3(E9,F9,G9),2)) This did not work: =IF(AND(ISBLANK(E9),ISBLANK(F9),ISBLANK(G9)),"",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))))

06-06-2005, 07:51 AM	#6 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	This formula seems to work: =IF(H9="","",IF(H9=E9,E$8,IF(H9=F9,F$8,IF(H9=G9,G$8,""))))

06-06-2005, 08:57 AM	#8 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	ReeKorl.... The Whold Project works great now, but I have a slight problem where some figures after lines 85 are not dropping in even though the formula is ok. Then after a another section, no figures are inputted. I have changed this far from the test one and do not realy want to put it on the site for all to see, is there a way I can send it to you to look at?

06-07-2005, 02:42 AM	#10 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	OK, two updates here. Some of the lines aren't dropping in because vendor1's prices sheet had blanks in the cost section - put them in as 0's instead and it works. Next, in some cases (for some strange reason) the VLookup brings back values slightly different to their true values from the prices sheet. It's probably the decimal->binary->decimal conversion again. This is stopping the correct lowest vendor text being produced. In columns E F and G on the Inventory page, you'll need to put this in: Code: =round(original formula goes here,2) This will round the values back to the nearest cent and allow the checker to notice they are actually equal. It's highly unlikely the error will ever be more than half a cent, but if you do find it happens, you can use the rounddown function instead. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-07-2005, 06:26 AM	#11 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	I am a dummy, I did not see I had missed putting them in. I changed the array numbers to increase the length of the sheet, but the program stops putting data into the Inventory sheet at line 171 or item code 10164. Last edited by 00Seven; 06-07-2005 at 06:43 AM.

06-07-2005, 11:46 AM	#12 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Never mind I think I got it. I was thinking in the wrong section, it was not the VB, it was an ajustmetn in the sheets and formula. Gosh I must be a pain in the but sometimes.

06-07-2005, 12:08 PM	#13 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Not at all, I'm glad to help Glad you got it working. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."