Hi,

I have created the following equation using VLOOKUP...

=VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)

The result varies depends on my data, but I would like any result that is $0
to equal "***"

Does anyone have any ideas?

Thanks so much for your help!!

Hi Brent,

If you are only wanting results that are under a certain amount you can use:

=if(VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE)A1,0,VLOOKUP(D26,' Sales
Averages'!$A$4:$M$5000,4,FALSE))

I placed the "upper limit" in cell A1.

If you are wanting ALL values under that amount you should use a filter.

Squeaky.



"Brent" wrote:

Hi,

I have created the following equation using VLOOKUP...

=VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)

The result varies depends on my data, but I would like any result that is $0
to equal "***"

Does anyone have any ideas?

Thanks so much for your help!!

Try this:

=IF(VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE)=0,"***",VLOOKUP(D2 6,'Sales
Averages'!$A$4:$M$5000,4,FALSE))


"Brent" wrote:

Hi,

I have created the following equation using VLOOKUP...

=VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)

The result varies depends on my data, but I would like any result that is $0
to equal "***"

Does anyone have any ideas?

Thanks so much for your help!!

You may want to keep the result 0, but show the asterisks.

You could select the range with this formula and use:

Format|Cells|Number Tab
Select Custom Category
type:
$#,##0.00;-$#,##0.00;"***"

The format is: positive;negative;zero;text

Change the format to what you really like for the non-zero values.

You may want to try just hitting the $ on the formatting toolbar (xl2003). This
applies a currency format that shows a hyphen for 0's.

By displaying *** (or -), you may find that subsequent arithmetic formulas stay
nice and easy.

Brent wrote:

Hi,

I have created the following equation using VLOOKUP...

=VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)

The result varies depends on my data, but I would like any result that is $0
to equal "***"

Does anyone have any ideas?

Thanks so much for your help!!

--

Dave Peterson