Increase value of number in formula/using named cells. And use ofIF sentence

[Mrmojo]

Hello. I am new to this group and will try to explain two problems. Hope
you understand my poor english. My questions are probably easy for most
of you, so I hope someone will help
Problem one.
I am making a spreadsheet where I have to use names not cell numbers
(can not use B1, C3 and so on). I shall use the f(X)=ax3 + bx2 + cx + d
formula (the number 3 and 2 are exponents -if that is what is it called).
I am going to write a start value (lets name the cell Start), a step
value (Step) and a,b,c and d. The last four cells are named a, b, c_ and
d. All this is quite all right but i am going to calculate the formula
in 30 steps where the step value changes the value of the Start value
with the value i wrote (ex if Step is 0,2 the Start value increases with
0,2 each step down the colum - from lets say -4 to pluss 1,8). I have
the start value in a column on the left of the column with the
calculation. Okey so if I could use the cell numbers it would be easy.
But as it is now I have not found a solution. If I could start by using
start+step*0 in the formula and then autoincrease the one number with
1 when copying the formula down the colum i would be saved. But I can
not find a way to do this. Can anyone help ( or suggest another
solution) I would be really pleased.
Problem 2.
I am going to write a program to calculate tax groups. I must use the IF
sentence and the whole calculation is going to be made in the one
formula. The problem is as follows. If the input is, lets say, up to
20000 there is no extra tax (answer is 0), if you earn more than 20000
and up to 30000 there should be calculated 13,5% tax on the value
between 20000 and 30000. If you earn More than 30000 you must pay 19,8%
tax of the sum above 30000 and of course 13,5% of the 10000 between 2000
and 30000. I have the formulas (I think) Taxes=(income-20000)*13,5%
(between 20000 and 30000) and Taxes=10000*13,5% + (income-30000)*19,5%
(above 30000) but I am uncertain on how to use the IF sentence to solve
it all in one fomula (or one cell). Can anyone help.

 

[Roger Govier]

Hi

One
I'm not sure I fully understand what you are looking for, but perhaps
the following will help.
If you need a way to step up a number as you go down a column, take a
look at the ROW() function.
This returns the row number of the cell in which the formula is used

A1 = 20*ROW() would return 20 i.e. 20 x 1, but the same formula in cell
A5 would return 100, i.e. 20 x 5

Two
With your salary in cell A1
=(A1-20000)*13.5%+MAX(A1-30000,0)*6.3%

I have used 19.8% as the top rate of tax (13.5 + 6.3) as that is the
figure you use in the first part of your description.
Later, you refer to 19.5%. If 19.5% i correct, amend the formula above
to show 6% at the end, rather than 6.3%

Regards

Roger Govier

 

[Mrmojo]

Thank you for trying to help.
I see now that I have used the words "write a program" in problem two.
That is of course wrong. I am going to make a spreadsheet. The two
problems refers to two different spreadsheets. Its teh first one thats
need to stp up a number in a formula.
In the second one the problem is how to use the an IF sentence to solve
the tax problem. 19,8 or 19,5 does not really matter as it just is an
example but of course I ment 19,5 all the way.
I seems I still need som help wit this problem.

 

[Roger Govier]

Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier

 

[Mrmojo]

It gives an "error in formula message". But the point was that I has to
use the IF function. But thanks again.
Another question. What about the problem with the cell named Start and
Step. I have a formula that is saying Start+Step. When I copy this
fomula down the column it is supposed to increase the value in the cell
with the value that i wrote in the Step cell ( ex 0,2). As it is now I
get the same number in every cell when i copy. I think I should use the
$ one way or another but i am completely lost.

Roger Govier skrev:

 

[Mrmojo]

Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers is
wrong. Of course I have to use the norwegian version of the MAX command
but that is not relevant. I put in an IF sentence to get the answer 0
when there is no top tax to calculate (income lower or equal to 354300).
The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any suggestions?

 

[Roger Govier]

Hi

Try
=MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)

Once again, I repeat, you do NOT need to use an IF statement for this to
work.
The first MAX function, now takes care of the situation that he salary
is less than the starting point for any tax to be paid.
Obviously, you need to use the Norwegian translation of MAX.

I am still not sure that I understand your step function problem.
However, if it is that you want to increase the step by 0.2 as you copy
the formula down a column, you could perhaps make use of the ROW()
function (or Norwegian equivalent).

For example the following formula in A1, and a value of 20 in B1
=$B$1*Row()*0.2 would multiply 20 by row number (1) by 0.2 and give a
result of 4
When copied down to A2, ROW() would return 2, so the result would be 8,
and so on as you go down the column.
Does this help?

Regards

Roger Govier

Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers
is wrong. Of course I have to use the norwegian version of the MAX
command but that is not relevant. I put in an IF sentence to get the
answer 0 when there is no top tax to calculate (income lower or equal
to 354300). The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any
suggestions?

Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier

 

[Is that a fact?]

Hi

No it idd not work. Excel will not accept the comma after the 0 after
the first MAX(0, =MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)
If I change the , to an * the formula calculates wrong.

Roger Govier skrev:

Hi

Try
=MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)

Once again, I repeat, you do NOT need to use an IF statement for this to
work.
The first MAX function, now takes care of the situation that he salary
is less than the starting point for any tax to be paid.
Obviously, you need to use the Norwegian translation of MAX.

I am still not sure that I understand your step function problem.
However, if it is that you want to increase the step by 0.2 as you copy
the formula down a column, you could perhaps make use of the ROW()
function (or Norwegian equivalent).

For example the following formula in A1, and a value of 20 in B1
=$B$1*Row()*0.2 would multiply 20 by row number (1) by 0.2 and give a
result of 4
When copied down to A2, ROW() would return 2, so the result would be 8,
and so on as you go down the column.
Does this help?

Regards

Roger Govier

Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers
is wrong. Of course I have to use the norwegian version of the MAX
command but that is not relevant. I put in an IF sentence to get the
answer 0 when there is no top tax to calculate (income lower or equal
to 354300). The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any
suggestions?

Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier



Mrmojo wrote:

Thank you for trying to help.
I see now that I have used the words "write a program" in problem
two. That is of course wrong. I am going to make a spreadsheet. The
two problems refers to two different spreadsheets. Its teh first one
thats need to stp up a number in a formula.
In the second one the problem is how to use the an IF sentence to solve
the tax problem. 19,8 or 19,5 does not really matter as it just is
an example but of course I ment 19,5 all the way.
I seems I still need som help wit this problem.

Roger Govier wrote:

Hi

One
I'm not sure I fully understand what you are looking for, but
perhaps the following will help.
If you need a way to step up a number as you go down a column, take
a look at the ROW() function.
This returns the row number of the cell in which the formula is used

A1 = 20*ROW() would return 20 i.e. 20 x 1, but the same formula in
cell A5 would return 100, i.e. 20 x 5

Two
With your salary in cell A1
=(A1-20000)*13.5%+MAX(A1-30000,0)*6.3%

I have used 19.8% as the top rate of tax (13.5 + 6.3) as that is
the figure you use in the first part of your description.
Later, you refer to 19.5%. If 19.5% i correct, amend the formula
above to show 6% at the end, rather than 6.3%

Regards

Roger Govier



Mrmojo wrote:

Hello. I am new to this group and will try to explain two
problems. Hope you understand my poor english. My questions are
probably easy for most of you, so I hope someone will help
Problem one.
I am making a spreadsheet where I have to use names not cell
numbers (can not use B1, C3 and so on). I shall use the f(X)=ax3
+ bx2 + cx + d formula (the number 3 and 2 are exponents -if that
is what is it called).
I am going to write a start value (lets name the cell Start), a
step value (Step) and a,b,c and d. The last four cells are named
a, b, c_ and d. All this is quite all right but i am going to
calculate the formula in 30 steps where the step value changes the
value of the Start value with the value i wrote (ex if Step is 0,2
the Start value increases with 0,2 each step down the colum - from
lets say -4 to pluss 1,8). I have the start value in a column on
the left of the column with the calculation. Okey so if I could
use the cell numbers it would be easy. But as it is now I have not
found a solution. If I could start by using start+step*0 in the
formula and then autoincrease the one number with 1 when copying
the formula down the colum i would be saved. But I can not find a
way to do this. Can anyone help ( or suggest another solution) I
would be really pleased.
Problem 2.
I am going to write a program to calculate tax groups. I must use
the IF sentence and the whole calculation is going to be made in
the one formula. The problem is as follows. If the input is, lets
say, up to 20000 there is no extra tax (answer is 0), if you earn
more than 20000 and up to 30000 there should be calculated 13,5%
tax on the value between 20000 and 30000. If you earn More than
30000 you must pay 19,8% tax of the sum above 30000 and of course
13,5% of the 10000 between 2000 and 30000. I have the formulas (I
think) Taxes=(income-20000)*13,5% (between 20000 and 30000) and
Taxes=10000*13,5% + (income-30000)*19,5% (above 30000) but I am
uncertain on how to use the IF sentence to solve it all in one
fomula (or one cell). Can anyone help.

 

[Dave Peterson]

If your list separator is a semicolon, try:

=MAX(0;(A1-354300)*13.5%+MAX(A1-552600;0)*6.3%)

(Differences in windows settings can be irritating, huh?)

Hi

No it idd not work. Excel will not accept the comma after the 0 after
the first MAX(0, =MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)
If I change the , to an * the formula calculates wrong.

Roger Govier skrev:

Hi

Try
=MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)

Once again, I repeat, you do NOT need to use an IF statement for this to
work.
The first MAX function, now takes care of the situation that he salary
is less than the starting point for any tax to be paid.
Obviously, you need to use the Norwegian translation of MAX.

I am still not sure that I understand your step function problem.
However, if it is that you want to increase the step by 0.2 as you copy
the formula down a column, you could perhaps make use of the ROW()
function (or Norwegian equivalent).

For example the following formula in A1, and a value of 20 in B1
=$B$1*Row()*0.2 would multiply 20 by row number (1) by 0.2 and give a
result of 4
When copied down to A2, ROW() would return 2, so the result would be 8,
and so on as you go down the column.
Does this help?

Regards

Roger Govier

Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers
is wrong. Of course I have to use the norwegian version of the MAX
command but that is not relevant. I put in an IF sentence to get the
answer 0 when there is no top tax to calculate (income lower or equal
to 354300). The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any
suggestions?



Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier



Mrmojo wrote:

Thank you for trying to help.
I see now that I have used the words "write a program" in problem
two. That is of course wrong. I am going to make a spreadsheet. The
two problems refers to two different spreadsheets. Its teh first one
thats need to stp up a number in a formula.
In the second one the problem is how to use the an IF sentence to solve
the tax problem. 19,8 or 19,5 does not really matter as it just is
an example but of course I ment 19,5 all the way.
I seems I still need som help wit this problem.

Roger Govier wrote:

Hi

One
I'm not sure I fully understand what you are looking for, but
perhaps the following will help.
If you need a way to step up a number as you go down a column, take
a look at the ROW() function.
This returns the row number of the cell in which the formula is used

A1 = 20*ROW() would return 20 i.e. 20 x 1, but the same formula in
cell A5 would return 100, i.e. 20 x 5

Two
With your salary in cell A1
=(A1-20000)*13.5%+MAX(A1-30000,0)*6.3%

I have used 19.8% as the top rate of tax (13.5 + 6.3) as that is
the figure you use in the first part of your description.
Later, you refer to 19.5%. If 19.5% i correct, amend the formula
above to show 6% at the end, rather than 6.3%

Regards

Roger Govier



Mrmojo wrote:

Hello. I am new to this group and will try to explain two
problems. Hope you understand my poor english. My questions are
probably easy for most of you, so I hope someone will help
Problem one.
I am making a spreadsheet where I have to use names not cell
numbers (can not use B1, C3 and so on). I shall use the f(X)=ax3
+ bx2 + cx + d formula (the number 3 and 2 are exponents -if that
is what is it called).
I am going to write a start value (lets name the cell Start), a
step value (Step) and a,b,c and d. The last four cells are named
a, b, c_ and d. All this is quite all right but i am going to
calculate the formula in 30 steps where the step value changes the
value of the Start value with the value i wrote (ex if Step is 0,2
the Start value increases with 0,2 each step down the colum - from
lets say -4 to pluss 1,8). I have the start value in a column on
the left of the column with the calculation. Okey so if I could
use the cell numbers it would be easy. But as it is now I have not
found a solution. If I could start by using start+step*0 in the
formula and then autoincrease the one number with 1 when copying
the formula down the colum i would be saved. But I can not find a
way to do this. Can anyone help ( or suggest another solution) I
would be really pleased.
Problem 2.
I am going to write a program to calculate tax groups. I must use
the IF sentence and the whole calculation is going to be made in
the one formula. The problem is as follows. If the input is, lets
say, up to 20000 there is no extra tax (answer is 0), if you earn
more than 20000 and up to 30000 there should be calculated 13,5%
tax on the value between 20000 and 30000. If you earn More than
30000 you must pay 19,8% tax of the sum above 30000 and of course
13,5% of the 10000 between 2000 and 30000. I have the formulas (I
think) Taxes=(income-20000)*13,5% (between 20000 and 30000) and
Taxes=10000*13,5% + (income-30000)*19,5% (above 30000) but I am
uncertain on how to use the IF sentence to solve it all in one
fomula (or one cell). Can anyone help.

 

[Roger Govier]

Thanks Dave

(Differences in windows settings can be irritating, huh?)
Absolutely. Why doesn't all the world do things the same as the Brits.!!!!<vbg>

My fault, I did mention changing to the Norwegian version of MAX, but totally forget to mention local separators. I am sure this has now sorted it out for the OP.


Regards

Roger Govier

If your list separator is a semicolon, try:

=MAX(0;(A1-354300)*13.5%+MAX(A1-552600;0)*6.3%)

(Differences in windows settings can be irritating, huh?)



Is that a fact? wrote:

Hi

No it idd not work. Excel will not accept the comma after the 0 after
the first MAX(0, =MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)
If I change the , to an * the formula calculates wrong.

Roger Govier skrev:

Hi

Try
=MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)

Once again, I repeat, you do NOT need to use an IF statement for this to
work.
The first MAX function, now takes care of the situation that he salary
is less than the starting point for any tax to be paid.
Obviously, you need to use the Norwegian translation of MAX.

I am still not sure that I understand your step function problem.
However, if it is that you want to increase the step by 0.2 as you copy
the formula down a column, you could perhaps make use of the ROW()
function (or Norwegian equivalent).

For example the following formula in A1, and a value of 20 in B1
=$B$1*Row()*0.2 would multiply 20 by row number (1) by 0.2 and give a
result of 4
When copied down to A2, ROW() would return 2, so the result would be 8,
and so on as you go down the column.
Does this help?

Regards

Roger Govier



Mrmojo wrote:



Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers
is wrong. Of course I have to use the norwegian version of the MAX
command but that is not relevant. I put in an IF sentence to get the
answer 0 when there is no top tax to calculate (income lower or equal
to 354300). The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any
suggestions?





Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier



Mrmojo wrote:



Thank you for trying to help.
I see now that I have used the words "write a program" in problem
two. That is of course wrong. I am going to make a spreadsheet. The
two problems refers to two different spreadsheets. Its teh first one
thats need to stp up a number in a formula.
In the second one the problem is how to use the an IF sentence to solve
the tax problem. 19,8 or 19,5 does not really matter as it just is
an example but of course I ment 19,5 all the way.
I seems I still need som help wit this problem.

Roger Govier wrote:



Hi

One
I'm not sure I fully understand what you are looking for, but
perhaps the following will help.
If you need a way to step up a number as you go down a column, take
a look at the ROW() function.
This returns the row number of the cell in which the formula is used

A1 = 20*ROW() would return 20 i.e. 20 x 1, but the same formula in
cell A5 would return 100, i.e. 20 x 5

Two
With your salary in cell A1
=(A1-20000)*13.5%+MAX(A1-30000,0)*6.3%

I have used 19.8% as the top rate of tax (13.5 + 6.3) as that is
the figure you use in the first part of your description.
Later, you refer to 19.5%. If 19.5% i correct, amend the formula
above to show 6% at the end, rather than 6.3%

Regards

Roger Govier



Mrmojo wrote:



Hello. I am new to this group and will try to explain two
problems. Hope you understand my poor english. My questions are
probably easy for most of you, so I hope someone will help
Problem one.
I am making a spreadsheet where I have to use names not cell
numbers (can not use B1, C3 and so on). I shall use the f(X)=ax3
+ bx2 + cx + d formula (the number 3 and 2 are exponents -if that
is what is it called).
I am going to write a start value (lets name the cell Start), a
step value (Step) and a,b,c and d. The last four cells are named
a, b, c_ and d. All this is quite all right but i am going to
calculate the formula in 30 steps where the step value changes the
value of the Start value with the value i wrote (ex if Step is 0,2
the Start value increases with 0,2 each step down the colum - from
lets say -4 to pluss 1,8). I have the start value in a column on
the left of the column with the calculation. Okey so if I could
use the cell numbers it would be easy. But as it is now I have not
found a solution. If I could start by using start+step*0 in the
formula and then autoincrease the one number with 1 when copying
the formula down the colum i would be saved. But I can not find a
way to do this. Can anyone help ( or suggest another solution) I
would be really pleased.
Problem 2.
I am going to write a program to calculate tax groups. I must use
the IF sentence and the whole calculation is going to be made in
the one formula. The problem is as follows. If the input is, lets
say, up to 20000 there is no extra tax (answer is 0), if you earn
more than 20000 and up to 30000 there should be calculated 13,5%
tax on the value between 20000 and 30000. If you earn More than
30000 you must pay 19,8% tax of the sum above 30000 and of course
13,5% of the 10000 between 2000 and 30000. I have the formulas (I
think) Taxes=(income-20000)*13,5% (between 20000 and 30000) and
Taxes=10000*13,5% + (income-30000)*19,5% (above 30000) but I am
uncertain on how to use the IF sentence to solve it all in one
fomula (or one cell). Can anyone help.

 

[Is that a fact?]

It did the trick. Thank you.

Dave Peterson skrev:

If your list separator is a semicolon, try:

=MAX(0;(A1-354300)*13.5%+MAX(A1-552600;0)*6.3%)

(Differences in windows settings can be irritating, huh?)

Hi

No it idd not work. Excel will not accept the comma after the 0 after
the first MAX(0, =MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)
If I change the , to an * the formula calculates wrong.

Roger Govier skrev:

Hi

Try
=MAX(0,(A1-354300)*13.5%+MAX(A1-552600,0)*6.3%)

Once again, I repeat, you do NOT need to use an IF statement for this to
work.
The first MAX function, now takes care of the situation that he salary
is less than the starting point for any tax to be paid.
Obviously, you need to use the Norwegian translation of MAX.

I am still not sure that I understand your step function problem.
However, if it is that you want to increase the step by 0.2 as you copy
the formula down a column, you could perhaps make use of the ROW()
function (or Norwegian equivalent).

For example the following formula in A1, and a value of 20 in B1
=$B$1*Row()*0.2 would multiply 20 by row number (1) by 0.2 and give a
result of 4
When copied down to A2, ROW() would return 2, so the result would be 8,
and so on as you go down the column.
Does this help?

Regards

Roger Govier



Mrmojo wrote:


Hi once again.

I have solved the problem using IF
[=IF(D6<354300;"0";IF(D6<552600;(D6-354300)*13,5%;198300*13,5%+(D6-552600)*19,5%))]
but I gained interest in your fomula. It works if the income is above
the max limit ( i have used 552600 NKR as the max limit and 354300 as
the lower trigging point ). But between 354300 and 552600 the answers
is wrong. Of course I have to use the norwegian version of the MAX
command but that is not relevant. I put in an IF sentence to get the
answer 0 when there is no top tax to calculate (income lower or equal
to 354300). The formula now looks like this
=IF(D6<=354300;"0";(D6-354300)*13,5%+MAX(D6-552600)*6%
But the problem is when the income is >354200 and <=552600.
As an example the income 400000 returns 2986,5 while the answer should
be 6169,5. I actually is quite keen on finding a solution. Any
suggestions?




Hi

Did you try the formula I gave you for the tax calculation?
It resolves the problem without having to use IF statements.
You can do it with IF statements, but why, if you can get the correct
result without?

Regards

Roger Govier



Mrmojo wrote:


Thank you for trying to help.
I see now that I have used the words "write a program" in problem
two. That is of course wrong. I am going to make a spreadsheet. The
two problems refers to two different spreadsheets. Its teh first one
thats need to stp up a number in a formula.
In the second one the problem is how to use the an IF sentence to solve
the tax problem. 19,8 or 19,5 does not really matter as it just is
an example but of course I ment 19,5 all the way.
I seems I still need som help wit this problem.

Roger Govier wrote:


Hi

One
I'm not sure I fully understand what you are looking for, but
perhaps the following will help.
If you need a way to step up a number as you go down a column, take
a look at the ROW() function.
This returns the row number of the cell in which the formula is used

A1 = 20*ROW() would return 20 i.e. 20 x 1, but the same formula in
cell A5 would return 100, i.e. 20 x 5

Two
With your salary in cell A1
=(A1-20000)*13.5%+MAX(A1-30000,0)*6.3%

I have used 19.8% as the top rate of tax (13.5 + 6.3) as that is
the figure you use in the first part of your description.
Later, you refer to 19.5%. If 19.5% i correct, amend the formula
above to show 6% at the end, rather than 6.3%

Regards

Roger Govier



Mrmojo wrote:


Hello. I am new to this group and will try to explain two
problems. Hope you understand my poor english. My questions are
probably easy for most of you, so I hope someone will help
Problem one.
I am making a spreadsheet where I have to use names not cell
numbers (can not use B1, C3 and so on). I shall use the f(X)=ax3
+ bx2 + cx + d formula (the number 3 and 2 are exponents -if that
is what is it called).
I am going to write a start value (lets name the cell Start), a
step value (Step) and a,b,c and d. The last four cells are named
a, b, c_ and d. All this is quite all right but i am going to
calculate the formula in 30 steps where the step value changes the
value of the Start value with the value i wrote (ex if Step is 0,2
the Start value increases with 0,2 each step down the colum - from
lets say -4 to pluss 1,8). I have the start value in a column on
the left of the column with the calculation. Okey so if I could
use the cell numbers it would be easy. But as it is now I have not
found a solution. If I could start by using start+step*0 in the
formula and then autoincrease the one number with 1 when copying
the formula down the colum i would be saved. But I can not find a
way to do this. Can anyone help ( or suggest another solution) I
would be really pleased.
Problem 2.
I am going to write a program to calculate tax groups. I must use
the IF sentence and the whole calculation is going to be made in
the one formula. The problem is as follows. If the input is, lets
say, up to 20000 there is no extra tax (answer is 0), if you earn
more than 20000 and up to 30000 there should be calculated 13,5%
tax on the value between 20000 and 30000. If you earn More than
30000 you must pay 19,8% tax of the sum above 30000 and of course
13,5% of the 10000 between 2000 and 30000. I have the formulas (I
think) Taxes=(income-20000)*13,5% (between 20000 and 30000) and
Taxes=10000*13,5% + (income-30000)*19,5% (above 30000) but I am
uncertain on how to use the IF sentence to solve it all in one
fomula (or one cell). Can anyone help.