Calculation Mishap

G

Guest

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!!
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

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.
 
D

David Biddulph

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).
 
G

Guest

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

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

Cells are formatted for "general"

Sandy Mann said:
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
D

Dave Peterson

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.
 
D

Dave Peterson

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

David Biddulph

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.
 
G

Guest

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

Dave Peterson

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

Guest

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!!
 
S

Sandy Mann

Loni - RWT said:
tax rate will calculate, why won't the surcharge rate?
Click to expand...

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

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Loni - RWT said:
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!!
Click to expand...
 
G

Guest

I changed it to 5 and it uses the 5.


Sandy Mann said:
Loni - RWT said:
tax rate will calculate, why won't the surcharge rate?
Click to expand...

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

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

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

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to return "n/a" when no match is found. 5
Sales Taxes 2
Combining Text From Cells 3
Calculating tax on rebates 2
Calculation confusion 2
Conditional Printing 2
copy data from one worksheet and disregard blank cells 6
Calculation of double taxe rates! 1

Top