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

[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 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]

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

[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:
> 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.
>>>
>>>

[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?



> 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]

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?]

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

[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?)



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

--

Dave Peterson

[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



Dave Peterson wrote:

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