two-variable data table question

[Judy]

I have inherited the spreadsheet below containing a two-variable data table.
Can someone tell me what the 1-10 portion of the formula means; I can't find
what it is relating to.

The formula in cell B2 is: =A3*1%*(1-10*B2)

A B C D E
F G
1 Expense vs. Sales
2 0 1% 2% 4% 6% 8%
3 Sales $1,000,000 9000 8000 6000 4000 2000
4 $2,000,000 18000 16000 12000 8000 4000
5 $3,000,000 27000 24000 18000 12000 6000
6 $4,000,000 36000 32000 24000 16000 8000
7 $5,000,000 45000 40000 30000 20000 10000

Thanks for the help.

 

[Rasoul Khoshravan]

I have inherited the spreadsheet below containing a two-variable data table.
Can someone tell me what the 1-10 portion of the formula means; I can't find
what it is relating to.

The formula in cell B2 is:  =A3*1%*(1-10*B2)

     A          B             C               D              E              
F              G        
1               Expense vs. Sales                              
2       0       1%      2%      4%      6%      8%
3 Sales $1,000,000      9000    8000    6000    4000    2000
4       $2,000,000      18000   16000   12000   8000    4000
5       $3,000,000      27000   24000   18000   12000   6000
6       $4,000,000      36000   32000   24000   16000   8000
7       $5,000,000      45000   40000   30000   20000   10000

Thanks for the help.

one minus 10 multiplied by B2

 

[Judy]

I figured 1-10 meant 1 minus 10, but where is that coming from in the
spreadsheet? I don't see any 10 of anything - what is the 1-10 relating to?

 

[Judy]

I figured the 1-10 meant 1 minus 10, but I don't know where that's coming
from. I don't see 10 of anything in the spreadsheet, so what is the 1 minus
10 relating to?

 

[David Biddulph]

It's not 1 minus 10, it's 1 - 10*B2
(Remember that multiplication has precedence over subtraction in the
precedence of arithmetic operators).

If you don't understand where the 10 comes from, how many percent do you
reduce A3*1% by for every 1% change in row 2?

 

[Judy]

I must really be dense or something because I'm not getting this at all. If
the 1-10 is not subtracting, then what number is being multiplied by B2?

What you are seeing in the copied spreadsheet is the entire spreadsheet.
There are not 10 rows, 10 columns, nor 10 figures, so I still don't
understand what the
1-10 relates to in the formula.

Thanks for the continued help.
Judy

 

[Mike Middleton]

Judy -

The formula =A3*1%*(1-10*B2) generally would mean to take the value in cell
B2, multiply it by 10, subtract the result from 1, multiply that result by
1%, and finally multiply that result by the value in cell A3.

But, if that formula is in cell B2, it would cause a circular reference, and
Excel usually would display an error message.

Maybe you should (a) ask the originator what was intended or (b) describe
what you would like to do and start over.

- Mike Middleton
http://www.MikeMiddleton.com

 

[austris]

In case you genuinely aren't kidding then what that (1 - 10*B2) means
is that the formula:
(1) takes value from cell B2
(2) multiplies the value by 10
(3) subtracts the multiplication [B2*10] from 1 thus
one minus (ten multiplied by value in B2)


What I wouldn't be too sure about, however, is whether in your
particular instance you need the circular reference - i.e., you're
saying that you've got the formula in Cell B2 but at the same time the
formula takes value from the same cell (B2)... Although, there are
valid scenarios for Circular references but that's you're call if
yours is the one.
Not sure if i helped.

 

[Rasoul Khoshravan]

I must really be dense or something because I'm not getting this at all. If
the 1-10 is not subtracting, then what number is being multiplied by B2?

What you are seeing in the copied spreadsheet is the entire spreadsheet.
There are not 10 rows, 10 columns, nor 10 figures, so I still don't
understand what the
1-10 relates to in the formula.

Thanks for the continued help.
Judy

The "10" in that phrase is a constant, given by the designer of the
formula. You should ask the designer where and why does 10 come from?
If you are familiar with analytical calculus, any equation of type a*x
+b means a straight line in Cartesian coordinates, where "a" & "b" are
constants. HTH

 

[David Biddulph]

My answers to your most recent points are interspersed in your post,
--
David Biddulph

I must really be dense or something because I'm not getting this at
all. If the 1-10 is not subtracting, then what number is being
multiplied by B2?

Maybe you missed reading the bit to which you were replying:
"> It's not 1 minus 10, it's 1 - 10*B2

(Remember that multiplication has precedence over subtraction in the
precedence of arithmetic operators)."

Would it be easier if I added parentheses?
1 - (10*B2)
but we didn't need the parentheses because:
"> (Remember that multiplication has precedence over subtraction in the

precedence of arithmetic operators)."

If this idea of precedence is difficult for you, it is explained in Excel
help at:
http://office.microsoft.com/en-us/excel/HP100788861033.aspx

What you are seeing in the copied spreadsheet is the entire
spreadsheet. There are not 10 rows, 10 columns, nor 10 figures, so I
still don't understand what the
1-10 relates to in the formula.

I didn't say anything about 10 rows or 10 columns or 10 figures, I said:

"> If you don't understand where the 10 comes from, how many percent do you

reduce A3*1% by for every 1% change in row 2?"

but you didn't try to answer my question, which is presumably why you didn't
understand what I was saying.

The answer to my question is that when B2 is 1%, you have subtracted 10%
from A3*1%.
When C2 is 2%, you subtract 20% from A3*1%.
The 1% in B2, when multiplied by 10, gives 10%, and that is what you are
subtracting from 1, before you multiply the result by A3*1% to get the
answer in B3. [Note that in your original question you said "The formula in
cell B2" where I am sure that you meant that that was the formula in B3.]
The 2% in C2, when multiplied by 10, gives 20%, and that is what you are
subtracting from 1, before you multiply the result by A3*1% to get the
answer in C3.

Is that getting clearer?