How Excel converts % sign in a cell and use in formula

[Guest]

I am trying to figure out a formula in Excel that I know is simple, but it's
been years....

=(B3+(B3*$F$8))*(1+$F$9)

I understand about the order of operations and what has to happen first and
the absolute cell reference. I'm confused about what the 1 is in the
(1+$F$9)...the info in cell F9 is sales tax and is written as 8.25%.

Thanks in advance for the help.

 

[joeu2004]

I am trying to figure out a formula in Excel that I know is simple, but it's
been years....

=(B3+(B3*$F$8))*(1+$F$9)

I understand about the order of operations and what has to happen first and
the absolute cell reference. I'm confused about what the 1 is in the
(1+$F$9)...the info in cell F9 is sales tax and is written as 8.25%.

8.25% is effectively 0.0825. So 1+$F$9 would be 1.0825. (Note: You
could also write 1+8.25%, and Excel will do the right thing.) If you
multiplied by only 8.25%, you would get the amount of sales tax. If
you multiply by 1+8.25%, you get the total balance; that is, the price
of the product sold plus the sales tax.

 

[joeu2004]

PS....

=(B3+(B3*$F$8))*(1+$F$9)

[....]
If you multiply by 1+8.25%, you get the total balance; that is, the price
of the product sold plus the sales tax.

By the same token, the formula could have been written:

=B3*(1+$F$8)*(1+$F$9)

which may or may not be clearer, depending on what F8 represents.

 

[Guest]

You're smart and thank you! With the 1, is this an Excel thing? How does
Excel know that the 1 represents the cost of product? Or is the answer that's
the way it was programmed and not to worry about it?

Thank you!

 

[Dana DeLouis]

...How does Excel know that the 1 represents the cost of product?

Hi. One way to look at it:
In general, the total cost might be:

Cost + Cost*Tax
but if we factor out the cost in the equation, we can write it as:
Cost*(1 + Tax)

Similar to what you see with
=B3+(B3*$F$8)
Mathematically, just factor out B3 to get your (1+F8)

 

[Guest]

Thanks. I see the logic of adding the cost to the tax.

What I don't understand is how Excel knows that the 1 represents $27.50
(cost of product).

I have to explain this to someone and they will ask how did I know to use
the 1 in the formula.

Thanks!

 

[Dave Peterson]

It's more like the 1 is equal to 100% of the product price--no matter what the
price, it's 100% of it.

 

[joeu2004]

Thanks. I see the logic of adding the cost to the tax.
What I don't understand is how Excel knows that the 1 represents $27.50
(cost of product).
I have to explain this to someone and they will ask how did I know to use
the 1 in the formula.

Think of "1" as meaning "one of this thing". So "1 + 8.25%" says "one
of this thing plus 8.25% of this thing". Isn't that how you
instinctively add sales tax to the price: you take "the price (this
thing) plus 8.25% of the price". Of course, in your case, "8.25%" is
replaced by the reference to F9. But hopefully that does not obscure
the meaning.

 

[JE McGimpsey]

Perhaps:

= B3 * (1 + Percent)

can be rearranged to the equivalent


= (B3 * 1) + (B3 * Percent)


so if you use 2, instead, you'll get


= (B3 * 2) + (B3 * Percent)


and your result will increase (though not quite double unless Percent is
zero).

XL doesn't know anything about connections or what any particular value
represents - it just multiplies and adds.

 

[joeu2004]

I understand the logic of what you're saying, mathematically.
But I'm not explaining myself well. The sale price is in B3, $27.50. How
does typing a 1 in the formula tell Excel that this represents the price?

Well, __mathematically__, the answer is derived from the following
rule:

a*(b+c) = a*b + a*c

Therefore, B3*(1+F9) is "27.50 plus 8.25% of 27.50" because it is the
same as B3*1 + B3*F9 -- and because B3 contains the sale price and F9
contains the sales tax rate, as your previous posting indicated you
understand.

And to reiterate the answer to the subject line of your posting, when
you write 8.25%, it is simply an alternative to writing 0.0825, which
is what Excel actually uses.

If I put a 2 in the formula, it doubles the price. How is Excel making this
connection?

Because B3*(2+F9) is B3*2 + B3*F9.

But beware: that is not the correct way to add the sales tax to double
the price. The correct way would be: 2*B3*(1+F9), which is the same
as 2*B3 + 2*B3*F9. The sales tax must be computed based on twice the
price.

The lightbulb for this is not on yet

That seemed quite clear from the start, so I tried to avoid a
mathematical explanation. If I may take one last stab at a
non-mathematical explanation....

How does typing a 1 in the formula tell Excel that this represents the price?

It doesn't. In the expression B3*(1+F9), "this thing" (1) is the value
of whatever is to the left of the asterisk ("*") -- subject to the
rules of order of operations, which you said you understand. Only the
human knows that B3 contains the sale price and F9 contains the sales
tax rate (percentage).

Perhaps I confused you by skipping a step. Excel translates B3*(1+F9)
into "this thing (B3) plus F9 times this thing (B3)". Alteratively, to
put it in the same terms that you have been using throughout this
thread: B3*(1+F9) means "this thing (the sale price in B3, $27.50)
plus the sales tax rate in F9 (8.25%) times this thing (the sale price
in B3, $27.50)".

As to why a*(b+c) = a*b + a*c, it is simply something that we must
accept as fact. In mathematics, it is called an axiom: a rule that
was demonstrated to be true long ago and never needs to be proved
again.

But if B3*(1+F9) is still impossible to gronk, simply rewrite it as B3
+ B3*F9. Hopefully the meaning of that will be obvious to "the other
person".