Make Formula SHORTER

TiggerToo · Mar 1st, 2002, 06:29 PM

I need this formula shorter and simpler.

=if(c6=11,"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,)))

column/row c6 has the code 11,
but if a "0" is put in place of 11, "#N/A" will appear. So I added, if c6=0,"" will make "#N/A" disappear.
Is there an easier way if c6 is blank or a "0" is place in c6.
I saw a posting someplace like =if(a1=11,a1=0,e4) I think it is an IF(or( type statement.

Dave Hawley · Mar 1st, 2002, 07:17 PM

Hi

Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options.

=IF(OR(C6=11,C6=0),"",VLOOKUP(C6,dutycode!$A$4:$D$14,4,))

=IF(COUNTIF(dutycode!$A$4:$A$14,C6)=0,"",VLOOKUP(C6,dutycode!$A$4:$D$14,4))

I alos note you haven't used False as the Range_lookup argument, you may want to change that?

Dave Hawley · Mar 1st, 2002, 07:18 PM

Hi

Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options.

=IF(OR(C6=11,C6=0),"",VLOOKUP(C6,dutycode!$A$4:$D$14,4,))

=IF(COUNTIF(dutycode!$A$4:$A$14,C6)=0,"",VLOOKUP(C6,dutycode!$A$4:$D$14,4))

I also note you haven't used False as the Range_lookup argument, you may want to change that?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 1st, 2002, 06:29 PM	#1
TiggerToo Board Regular Join Date: Feb 2002 Location: Macon, Georgia USA Posts: 124	I need this formula shorter and simpler. =if(c6=11,"",if(c6=0,"",vlookup(c6,dutycode!$a$4:$d$14,4,))) column/row c6 has the code 11, but if a "0" is put in place of 11, "#N/A" will appear. So I added, if c6=0,"" will make "#N/A" disappear. Is there an easier way if c6 is blank or a "0" is place in c6. I saw a posting someplace like =if(a1=11,a1=0,e4) I think it is an IF(or( type statement.

Mar 1st, 2002, 07:17 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options. =IF(OR(C6=11,C6=0),"",VLOOKUP(C6,dutycode!$A$4:$D$14,4,)) =IF(COUNTIF(dutycode!$A$4:$A$14,C6)=0,"",VLOOKUP(C6,dutycode!$A$4:$D$14,4)) I alos note you haven't used False as the Range_lookup argument, you may want to change that?

Mar 1st, 2002, 07:18 PM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Don't confuse shorter with better or more effiecient, often not the case. But here are 2 more options. =IF(OR(C6=11,C6=0),"",VLOOKUP(C6,dutycode!$A$4:$D$14,4,)) =IF(COUNTIF(dutycode!$A$4:$A$14,C6)=0,"",VLOOKUP(C6,dutycode!$A$4:$D$14,4)) I also note you haven't used False as the Range_lookup argument, you may want to change that?