Calculation Mishap
 

I have a workbook in which I have downloaded fuel tax rates for each state on
one sheet and on another sheet I'm trying to calculate actual tax due based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates based
on the state in cell A1. That function is working properly. However when I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However, if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance for
your help!!

Calculation Mishap
 

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!

Calculation Mishap
 

You are right, I you didn't have a number in A2 or A3 then yoou would get a
#Value error. Either A2 or A3 must be zero for you to get zero, or the
formatt of the cell isn't showing enough precision to give you the correct
answer

1) Try formating the number as number with more decimal places.
2) Check A2 and A3 to make usre they aren't zero.

"Loni - RWT" wrote:

I have a workbook in which I have downloaded fuel tax rates for each state on
one sheet and on another sheet I'm trying to calculate actual tax due based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates based
on the state in cell A1. That function is working properly. However when I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However, if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance for
your help!!

Calculation Mishap
 

Make sure that Tools/ Options/ Calculation is set to Automatic, not Manual.
If you're not sure whether A2 & A3 are text or number, check with the
formulae =ISNUMBER(A2) and =ISNUMBER(A3).
--
David Biddulph

"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!

Calculation Mishap
 

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!

Calculation Mishap
 

Thanks...calculation is set to automatic. =ISNUMBER(A2) returns 1 and
=ISNUMBER(A3) returns 0. What does that mean?

"David Biddulph" wrote:

Make sure that Tools/ Options/ Calculation is set to Automatic, not Manual.
If you're not sure whether A2 & A3 are text or number, check with the
formulae =ISNUMBER(A2) and =ISNUMBER(A3).
--
David Biddulph

"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!

Calculation Mishap
 

I would have expected TRUE and FALSE not 1 & 0. How are your cells
formatted?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each
state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have
one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for
the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have
a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However
when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero
every
time. I was thinking that maybe it was because the info was coming
from a
downloaded source & it wasn't recognizing the rate as a number.
However,
if
that were the case, wouldn't it give me an error message instead of
zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number.
Does
anyone have any ideas on what the difference might be? Thanks in
advance
for
your help!!

Calculation Mishap
 

Cells are formatted for "general"

"Sandy Mann" wrote:

I would have expected TRUE and FALSE not 1 & 0. How are your cells
formatted?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each
state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have
one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for
the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have
a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However
when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero
every
time. I was thinking that maybe it was because the info was coming
from a
downloaded source & it wasn't recognizing the rate as a number.
However,
if
that were the case, wouldn't it give me an error message instead of
zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number.
Does
anyone have any ideas on what the difference might be? Thanks in
advance
for
your help!!

Calculation Mishap
 

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!





--

Dave Peterson

Calculation Mishap
 

ps. You may want to make sure that the values in the lookup table are really
numeric--not text.

Loni - RWT wrote:

I have a workbook in which I have downloaded fuel tax rates for each state on
one sheet and on another sheet I'm trying to calculate actual tax due based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates based
on the state in cell A1. That function is working properly. However when I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However, if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance for
your help!!

--

Dave Peterson

Calculation Mishap
 

It says A3 isn't a number, so that's why your multiplication is giving zero.
If A3 is looking like a number, but is the result of your lookup, it sounds
as if your lookup must be finding text in the lookup array, not a number.
You may need to do the ISNUMBER() check on the elements of your lookup array
too.
--
David Biddulph

"Loni - RWT" wrote in message
...
=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each
state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have
one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for
the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have
a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However
when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero
every
time. I was thinking that maybe it was because the info was coming
from a
downloaded source & it wasn't recognizing the rate as a number.
However,
if
that were the case, wouldn't it give me an error message instead of
zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number.
Does
anyone have any ideas on what the difference might be? Thanks in
advance
for
your help!!

Calculation Mishap
 

Thanks! I unchecked the "transition formula evaluation" option and the cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!





--

Dave Peterson

Calculation Mishap
 

I'd still take the time to fix the original data--numbers that should be numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and the cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

I'm not sure how to make numbers numbers. On my downloaded tax rates sheet,
all of the cells appear to be numbers. However, when I do =isnumber, they
are all returning "false". And therefore, on my calculation sheet, all of
the cells with the rates from the lookup are returning "false" also. Here is
where I really get stumped...even though my tax rates are not numbers, once I
unchecked "transition formula evaluation" the calculation worked fine. Some
states have an additional surcharge rate, which I'm looking up from the
downloaded rates as well. It is returning the appropriate information -
still not as a number, but when I try to calculate my gallons * the surcharge
rate, it is giving me zero. I don't understand how the rate is calculating
and the surcharge is not when everything appears to be formatted the same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different things
& it doesn't seem to work. I keep coming back to the same question: If the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that should be numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and the cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for each state
on
one sheet and on another sheet I'm trying to calculate actual tax due
based
on miles traveled in each state. On my calculation worksheet, I have one
cell (A1) for the state, one cell (A2) for miles, one cell (A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell A3, I have a
vlookup to find the tax rate from worksheet with the downloaded rates
based
on the state in cell A1. That function is working properly. However when
I
try to return a value in cell A4, which is = A2*A3, it gives me zero every
time. I was thinking that maybe it was because the info was coming from a
downloaded source & it wasn't recognizing the rate as a number. However,
if
that were the case, wouldn't it give me an error message instead of zero?
Also, when I went to the worksheet with the downloaded info & did a
calculation, omitting the vlookup, the correct value was returned.
Therefore, it can't be that excel isn't recognizing it as a number. Does
anyone have any ideas on what the difference might be? Thanks in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

"Loni - RWT" wrote in message
...
tax rate will calculate, why won't the surcharge rate?

I don't know either but if you change:
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)

to:

Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,5,ROUND(D62,0)*I62)

does it use the 5?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I'm not sure how to make numbers numbers. On my downloaded tax rates
sheet,
all of the cells appear to be numbers. However, when I do =isnumber, they
are all returning "false". And therefore, on my calculation sheet, all of
the cells with the rates from the lookup are returning "false" also. Here
is
where I really get stumped...even though my tax rates are not numbers,
once I
unchecked "transition formula evaluation" the calculation worked fine.
Some
states have an additional surcharge rate, which I'm looking up from the
downloaded rates as well. It is returning the appropriate information -
still not as a number, but when I try to calculate my gallons * the
surcharge
rate, it is giving me zero. I don't understand how the rate is
calculating
and the surcharge is not when everything appears to be formatted the same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different
things
& it doesn't seem to work. I keep coming back to the same question: If
the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that should be
numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and the
cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for
each state
on
one sheet and on another sheet I'm trying to calculate actual
tax due
based
on miles traveled in each state. On my calculation worksheet,
I have one
cell (A1) for the state, one cell (A2) for miles, one cell
(A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell
A3, I have a
vlookup to find the tax rate from worksheet with the downloaded
rates
based
on the state in cell A1. That function is working properly.
However when
I
try to return a value in cell A4, which is = A2*A3, it gives me
zero every
time. I was thinking that maybe it was because the info was
coming from a
downloaded source & it wasn't recognizing the rate as a number.
However,
if
that were the case, wouldn't it give me an error message
instead of zero?
Also, when I went to the worksheet with the downloaded info &
did a
calculation, omitting the vlookup, the correct value was
returned.
Therefore, it can't be that excel isn't recognizing it as a
number. Does
anyone have any ideas on what the difference might be? Thanks
in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

I changed it to 5 and it uses the 5.


"Sandy Mann" wrote:

"Loni - RWT" wrote in message
...
tax rate will calculate, why won't the surcharge rate?

I don't know either but if you change:
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)

to:

Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,5,ROUND(D62,0)*I62)

does it use the 5?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I'm not sure how to make numbers numbers. On my downloaded tax rates
sheet,
all of the cells appear to be numbers. However, when I do =isnumber, they
are all returning "false". And therefore, on my calculation sheet, all of
the cells with the rates from the lookup are returning "false" also. Here
is
where I really get stumped...even though my tax rates are not numbers,
once I
unchecked "transition formula evaluation" the calculation worked fine.
Some
states have an additional surcharge rate, which I'm looking up from the
downloaded rates as well. It is returning the appropriate information -
still not as a number, but when I try to calculate my gallons * the
surcharge
rate, it is giving me zero. I don't understand how the rate is
calculating
and the surcharge is not when everything appears to be formatted the same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different
things
& it doesn't seem to work. I keep coming back to the same question: If
the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that should be
numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and the
cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I have a workbook in which I have downloaded fuel tax rates for
each state
on
one sheet and on another sheet I'm trying to calculate actual
tax due
based
on miles traveled in each state. On my calculation worksheet,
I have one
cell (A1) for the state, one cell (A2) for miles, one cell
(A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell
A3, I have a
vlookup to find the tax rate from worksheet with the downloaded
rates
based
on the state in cell A1. That function is working properly.
However when
I
try to return a value in cell A4, which is = A2*A3, it gives me
zero every
time. I was thinking that maybe it was because the info was
coming from a
downloaded source & it wasn't recognizing the rate as a number.
However,
if
that were the case, wouldn't it give me an error message
instead of zero?
Also, when I went to the worksheet with the downloaded info &
did a
calculation, omitting the vlookup, the correct value was
returned.
Therefore, it can't be that excel isn't recognizing it as a
number. Does
anyone have any ideas on what the difference might be? Thanks
in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

In which case we have to change I62 into anumber. Try:

=IF(ISNUMBER(--I62)=FALSE,0,ROUND(D62,0)*--I62)

If the value in I62 is text then the -- should turn the text in I62 into a
number in the test and in the cell.

If that doesn't wor then try:

=IF(ISNUMBER(--SUBSTITUTE(I62,CHAR(160),""))=FALSE,0,ROUND(D62,0) *--SUBSTITUTE(I62,CHAR(160),""))

Note that the Character number is 160 not I60. This should remove and HTML
non-breaking spaces and change the resulting text into a number
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I changed it to 5 and it uses the 5.


"Sandy Mann" wrote:

"Loni - RWT" wrote in message
...
tax rate will calculate, why won't the surcharge rate?

I don't know either but if you change:
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)

to:

Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,5,ROUND(D62,0)*I62)

does it use the 5?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I'm not sure how to make numbers numbers. On my downloaded tax rates
sheet,
all of the cells appear to be numbers. However, when I do =isnumber,
they
are all returning "false". And therefore, on my calculation sheet, all
of
the cells with the rates from the lookup are returning "false" also.
Here
is
where I really get stumped...even though my tax rates are not numbers,
once I
unchecked "transition formula evaluation" the calculation worked fine.
Some
states have an additional surcharge rate, which I'm looking up from the
downloaded rates as well. It is returning the appropriate
information -
still not as a number, but when I try to calculate my gallons * the
surcharge
rate, it is giving me zero. I don't understand how the rate is
calculating
and the surcharge is not when everything appears to be formatted the
same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working
fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different
things
& it doesn't seem to work. I keep coming back to the same question:
If
the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that should
be
numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and
the
cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula
evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in
message
...
I have a workbook in which I have downloaded fuel tax rates
for
each state
on
one sheet and on another sheet I'm trying to calculate
actual
tax due
based
on miles traveled in each state. On my calculation
worksheet,
I have one
cell (A1) for the state, one cell (A2) for miles, one cell
(A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell
A3, I have a
vlookup to find the tax rate from worksheet with the
downloaded
rates
based
on the state in cell A1. That function is working properly.
However when
I
try to return a value in cell A4, which is = A2*A3, it gives
me
zero every
time. I was thinking that maybe it was because the info was
coming from a
downloaded source & it wasn't recognizing the rate as a
number.
However,
if
that were the case, wouldn't it give me an error message
instead of zero?
Also, when I went to the worksheet with the downloaded info
&
did a
calculation, omitting the vlookup, the correct value was
returned.
Therefore, it can't be that excel isn't recognizing it as a
number. Does
anyone have any ideas on what the difference might be?
Thanks
in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

Thanks so much for your help!! I tried your first suggestion & it seems to
be working fine.

"Sandy Mann" wrote:

In which case we have to change I62 into anumber. Try:

=IF(ISNUMBER(--I62)=FALSE,0,ROUND(D62,0)*--I62)

If the value in I62 is text then the -- should turn the text in I62 into a
number in the test and in the cell.

If that doesn't wor then try:

=IF(ISNUMBER(--SUBSTITUTE(I62,CHAR(160),""))=FALSE,0,ROUND(D62,0) *--SUBSTITUTE(I62,CHAR(160),""))

Note that the Character number is 160 not I60. This should remove and HTML
non-breaking spaces and change the resulting text into a number
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I changed it to 5 and it uses the 5.


"Sandy Mann" wrote:

"Loni - RWT" wrote in message
...
tax rate will calculate, why won't the surcharge rate?

I don't know either but if you change:
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)

to:

Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,5,ROUND(D62,0)*I62)

does it use the 5?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I'm not sure how to make numbers numbers. On my downloaded tax rates
sheet,
all of the cells appear to be numbers. However, when I do =isnumber,
they
are all returning "false". And therefore, on my calculation sheet, all
of
the cells with the rates from the lookup are returning "false" also.
Here
is
where I really get stumped...even though my tax rates are not numbers,
once I
unchecked "transition formula evaluation" the calculation worked fine.
Some
states have an additional surcharge rate, which I'm looking up from the
downloaded rates as well. It is returning the appropriate
information -
still not as a number, but when I try to calculate my gallons * the
surcharge
rate, it is giving me zero. I don't understand how the rate is
calculating
and the surcharge is not when everything appears to be formatted the
same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working
fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different
things
& it doesn't seem to work. I keep coming back to the same question:
If
the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that should
be
numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option and
the
cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula
evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in
message
...
I have a workbook in which I have downloaded fuel tax rates
for
each state
on
one sheet and on another sheet I'm trying to calculate
actual
tax due
based
on miles traveled in each state. On my calculation
worksheet,
I have one
cell (A1) for the state, one cell (A2) for miles, one cell
(A3) for the
tax
rate and one cell (A4) that calculates the tax due. In cell
A3, I have a
vlookup to find the tax rate from worksheet with the
downloaded
rates
based
on the state in cell A1. That function is working properly.
However when
I
try to return a value in cell A4, which is = A2*A3, it gives
me
zero every
time. I was thinking that maybe it was because the info was
coming from a
downloaded source & it wasn't recognizing the rate as a
number.
However,
if
that were the case, wouldn't it give me an error message
instead of zero?
Also, when I went to the worksheet with the downloaded info
&
did a
calculation, omitting the vlookup, the correct value was
returned.
Therefore, it can't be that excel isn't recognizing it as a
number. Does
anyone have any ideas on what the difference might be?
Thanks
in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson

Calculation Mishap
 

I'm glad that you got it working but I would still do what Dave said and fix
the original data, it is always better to correct problems at source rather
than later on.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
Thanks so much for your help!! I tried your first suggestion & it seems
to
be working fine.

"Sandy Mann" wrote:

In which case we have to change I62 into anumber. Try:

=IF(ISNUMBER(--I62)=FALSE,0,ROUND(D62,0)*--I62)

If the value in I62 is text then the -- should turn the text in I62 into
a
number in the test and in the cell.

If that doesn't wor then try:

=IF(ISNUMBER(--SUBSTITUTE(I62,CHAR(160),""))=FALSE,0,ROUND(D62,0) *--SUBSTITUTE(I62,CHAR(160),""))

Note that the Character number is 160 not I60. This should remove and
HTML
non-breaking spaces and change the resulting text into a number
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I changed it to 5 and it uses the 5.


"Sandy Mann" wrote:

"Loni - RWT" wrote in message
...
tax rate will calculate, why won't the surcharge rate?

I don't know either but if you change:
Col J: Surcharge Amount
=IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)

to:

Col J: Surcharge Amount =IF(ISNUMBER(I62)=FALSE,5,ROUND(D62,0)*I62)

does it use the 5?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in message
...
I'm not sure how to make numbers numbers. On my downloaded tax rates
sheet,
all of the cells appear to be numbers. However, when I do
=isnumber,
they
are all returning "false". And therefore, on my calculation sheet,
all
of
the cells with the rates from the lookup are returning "false" also.
Here
is
where I really get stumped...even though my tax rates are not
numbers,
once I
unchecked "transition formula evaluation" the calculation worked
fine.
Some
states have an additional surcharge rate, which I'm looking up from
the
downloaded rates as well. It is returning the appropriate
information -
still not as a number, but when I try to calculate my gallons * the
surcharge
rate, it is giving me zero. I don't understand how the rate is
calculating
and the surcharge is not when everything appears to be formatted the
same
way. Here is what I've got:
Col A: State Abbreviation
Col B: Miles
Col D: Taxable Gallons
Col E: Actual Gallons purchased
Col F: Net Taxable Gallons (Col D-E)
Col G: Tax Rate =VLOOKUP($A62,CurrentTaxRates,2,FALSE)
Col H: Tax Due =ROUND(F62,0)*G62 **this calculation is working
fine
Col I: Surcharge Rate =VLOOKUP(CONCATENATE($A62 & "
schg"),CurrentTaxRates,2,FALSE)
Col J: Surcharge Amount
=IF(ISNUMBER(I62)=FALSE,0,ROUND(D62,0)*I62)
**this does not work
Col K: Total Due =SUM(H62+J62)

I appreciate any help you can give me. I've tried several different
things
& it doesn't seem to work. I keep coming back to the same question:
If
the
tax rate will calculate, why won't the surcharge rate? Thanks!!

"Dave Peterson" wrote:

I'd still take the time to fix the original data--numbers that
should
be
numbers
should be numbers <vbg.

Loni - RWT wrote:

Thanks! I unchecked the "transition formula evaluation" option
and
the
cell
calculated correctly.

"Dave Peterson" wrote:

Try this:
Tools|Options|Transition tab|uncheck "transition formula
evaluation"
(xl2003 menu system)

Your =isnumber() formulas will become true and false
and your =A2*A3 will either become #value! or a real number.

In fact, I'd uncheck all those transistion settings.

Loni - RWT wrote:

=ISNUMBER(A2) returns 1
=ISNUMBER(A3) returns 0

"Sandy Mann" wrote:

What do you get returned for:

=ISNUMBER(A2)
=ISNUMBER(A3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Loni - RWT" wrote in
message
...
I have a workbook in which I have downloaded fuel tax
rates
for
each state
on
one sheet and on another sheet I'm trying to calculate
actual
tax due
based
on miles traveled in each state. On my calculation
worksheet,
I have one
cell (A1) for the state, one cell (A2) for miles, one
cell
(A3) for the
tax
rate and one cell (A4) that calculates the tax due. In
cell
A3, I have a
vlookup to find the tax rate from worksheet with the
downloaded
rates
based
on the state in cell A1. That function is working
properly.
However when
I
try to return a value in cell A4, which is = A2*A3, it
gives
me
zero every
time. I was thinking that maybe it was because the info
was
coming from a
downloaded source & it wasn't recognizing the rate as a
number.
However,
if
that were the case, wouldn't it give me an error message
instead of zero?
Also, when I went to the worksheet with the downloaded
info
&
did a
calculation, omitting the vlookup, the correct value was
returned.
Therefore, it can't be that excel isn't recognizing it as
a
number. Does
anyone have any ideas on what the difference might be?
Thanks
in advance
for
your help!!





--

Dave Peterson


--

Dave Peterson