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

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.
0
10/4/2005 10:07:26 PM
excel 39879 articles. 2 followers. Follow

10 Replies
914 Views

Similar Articles

[PageSpeed] 27

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.
0
roger1272 (620)
10/5/2005 7:49:28 AM
  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.
0
10/5/2005 7:05:27 PM
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.
>>
0
roger1272 (620)
10/5/2005 8:16:51 PM
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.
>>>
>>>
0
10/5/2005 9:02:04 PM
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.
>>>
>>>
0
10/6/2005 8:31:57 PM
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.
>>>>
>>>>
>>>>
0
roger1272 (620)
10/7/2005 9:30:40 AM
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.
>>>>>
>>>>>
>>>>>
>>>>>
0
10/7/2005 1:50:22 PM
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
0
petersod (12005)
10/7/2005 2:03:17 PM
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.
>>>>>>>>                
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>              
>>>>>>>
>
>  
>
0
roger1272 (620)
10/7/2005 2:35:50 PM
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.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
> 
0
10/7/2005 6:15:28 PM
Reply:

Similar Artilces:

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Find and Replace Footnote Numbering
I often have documents where I need to put formatting before or after the footnote number for every footnote, often for hundreds of footnotes...anyone know how I can do this in one shot? I know I can specify ^f in find and replace, but only in the find portion...it gives an error if you put it in replace. ...

Count If Formula #4
I need a formula that will figure the following: If column C = 3, count all times < 00:45 If column C = 4, count all times < 00:45 If column C = 3, count all times > 00:46 Thanks. Your description is a bit confused.You have different actions for the same value (C=3). And where are the times, in a different column than C? So, making many assumptions, perhaps =SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0))) -- HTH RP (remove nothere from the email address if mailing direct) "Denise"...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

'top' is not a valid value for the 'vertical-align' property.
Well, it is, actually, and the resulting .chm file works properly in HTML Help. But it's a bit tedious getting that wrong useless error message from VS2002 on every build. (Well, not every build, it only does it sometimes actually.) Any idea how I can persuade it to stop telling me this? -- Tim Ward Brett Ward Limited - www.brettward.co.uk ...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Setting a dynamic range in a formula
Hi, I have a column of numbers and I always want the following arra formula to use the last 12 entries: =(PRODUCT(1+D1:D12/100)-1)*100 Any suggestions? Thanks, Phillycheese -- Phillycheese ----------------------------------------------------------------------- Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=37809 Assuming that Column D contains no blanks, try... =(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100 ...confirmed with CONTROL+SHIFT+ENTER. Hope th...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Formula for Game Spreadsheet
I am currenly playing a game of Ars Magica. I am using a spreadsheet to compute the info for my character. I am having a probllem with the casting total for spells related to a paticular character. The Formula is as follows : =IF(H13="","--",IF(J13="",INDIRECT(H13),MIN(INDIRECT(H13),INDIRECT(LEFT(J13,2)),INDIRECT(RIGHT(J13,2))))+IF(K13="",INDIRECT(I13),MIN(INDIRECT(I13),INDIRECT(LEFT(K13,2)),INDIRECT(RIGHT(K13,2))))+Sta+IF(N13="",0,$H$2)) I am trying to compute the casting total for the spells castable by this character. The total for the ...

how do i increase font size beyond 72
highlighting figure in font size box and trying to put larger number in myself does not do it. is there another way? thanks High-light the number, type your number, hit return. It that doesn't work for you, on the toolbar, format, font. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Polly Vincent" <Polly Vincent@discussions.microsoft.com> wrote in message news:9BDAEB5E-C30C-4721-BB51-F89C1B4332E2@microsoft.com... > highlighting figure in font ...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

random number generator
Hey is there a way to generate random numbers like you would for a draft. In other words, i have numbers 1 through 10 and generate a random order for those? Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 and copy it down through B10. Recalc (F9), then sort by column B. Hope this helps, Hutch "pat67" wrote: > Hey is there a way to generate random numbers like you would for a > draft. In other words, i have numbers 1 through 10 and generate a > random order for those? > . > You would have to generate 10 random numbers ...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

formula or VBA
I need to search through a column checking condtions. If the conditions are met, then I want to enter the corresponding value in the column next to it into a different cell. Basically, I want to search column A and, if the conditions are met, put the value in B into C. Would this be easier via formula or code? And maybe some starting suggestions would be nice if you don't mind. -- Thanks, Jim in a formula in col b =if(cond,a1,"") using vba for each c in range("a1:a100") if cond then c.offset(0,1)=c next -- Don Guillett SalesAid Software donaldb@281.com "...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Average of absolute values of moving ranges
I'm trying to get the average of the absolute values of a set of data over 8 weeks. Each week is on a seaparate sheet so to capture the moving ranges I've been using the formula below to get my result. Is there an easier way? =AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2)) Thanks! Amy The use of t...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

HOWTO: Customizing CRM
Hello, We would like to customize our CRM Opportunity form. We want to include a OpportunityNumber field, which will be auto-generated when the new opportunity is created, similar to auto-numbers for Contracts, Cases, Articles, Quotes, etc. Is it possible and how do we go about it? I've looked at CRM Deployment Manager. It's possible to create a simple custom Attribute for particular object. But I couldn't figure out how to make it an auto-generated number, like SQLServer Identity... Could I just modify OpportunityBase table in MSCRM database? Do I need to change METABASE databas...