Prev: Equations converted from 2007 to 2003 not sorting properly.
Next: reset defined ranges
From: Brent on 11 May 2010 14:06
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!!
From: Squeaky on 11 May 2010 14:23
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!!
From: Geoff on 11 May 2010 14:23
Try this:

=IF(VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE)=0,"***",VLOOKUP(D26,'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!!
From: Dave Peterson on 11 May 2010 15:26
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
 | 
Pages: 1
Prev: Equations converted from 2007 to 2003 not sorting properly.
Next: reset defined ranges