Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
Default Fun With That Wages Book Again

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby


  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #4   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 80
Default Fun With That Wages Book Again

Change your formula to read =IF(G12="","",H11+G12), and drag all the way
down. This way, H will remain blank until G gets a value. In your scenario,
H12 will show a value, but H13 will remain blank until you enter a value in
G13
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #5   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Kassie, that's working! Thanks very much for helping me. I've spent hours
trying to get around it, wondering what was wrong? Have you any idea why 'my
way' doesn't work? Thanks again.
Gatsby

"kassie" wrote:

Change your formula to read =IF(G12="","",H11+G12), and drag all the way
down. This way, H will remain blank until G gets a value. In your scenario,
H12 will show a value, but H13 will remain blank until you enter a value in
G13
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby





  #6   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 80
Default Fun With That Wages Book Again

That's a pleasure Gatsby! 'Your way' doen't work because something plus
nothing is still something!
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Kassie, that's working! Thanks very much for helping me. I've spent hours
trying to get around it, wondering what was wrong? Have you any idea why 'my
way' doesn't work? Thanks again.
Gatsby

"kassie" wrote:

Change your formula to read =IF(G12="","",H11+G12), and drag all the way
down. This way, H will remain blank until G gets a value. In your scenario,
H12 will show a value, but H13 will remain blank until you enter a value in
G13
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #7   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

I see now, Kassie. Thanks again. The formula you gave me has made entering
the wages so much easier. When I looked up Conditional Formatting they seemed
to be referring to how to change colours depending on values. They should
have written pages especially for ME. (HeHe).
Gatsby

"Gatsby" wrote:

Kassie, that's working! Thanks very much for helping me. I've spent hours
trying to get around it, wondering what was wrong? Have you any idea why 'my
way' doesn't work? Thanks again.
Gatsby

"kassie" wrote:

Change your formula to read =IF(G12="","",H11+G12), and drag all the way
down. This way, H will remain blank until G gets a value. In your scenario,
H12 will show a value, but H13 will remain blank until you enter a value in
G13
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #8   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 80
Default Fun With That Wages Book Again

That's the problem with the Excel help files! Never know what to look for hey.
Try help by typing in IF Worksheet function. How the heck you are supposed
to know that that is what you are looking for, beats me though! Still think
the best help available is this forum!
--
Hth

Kassie Kasselman


"Gatsby" wrote:

I see now, Kassie. Thanks again. The formula you gave me has made entering
the wages so much easier. When I looked up Conditional Formatting they seemed
to be referring to how to change colours depending on values. They should
have written pages especially for ME. (HeHe).
Gatsby

"Gatsby" wrote:

Kassie, that's working! Thanks very much for helping me. I've spent hours
trying to get around it, wondering what was wrong? Have you any idea why 'my
way' doesn't work? Thanks again.
Gatsby

"kassie" wrote:

Change your formula to read =IF(G12="","",H11+G12), and drag all the way
down. This way, H will remain blank until G gets a value. In your scenario,
H12 will show a value, but H13 will remain blank until you enter a value in
G13
--
Hth

Kassie Kasselman


"Gatsby" wrote:

Thanks for answering Gord.
Automatic is ticked and this still is happening. It seems to be only when
I'm doing accumulating that this problem arises. Any other ideas on how to
solve it? I'd appreciate it.
Gatsby

"Gord Dibben" wrote:

ToolsOptionsCalculation.

Set to Automatic.


Gord Dibben MS Excel MVP

On Sat, 6 Jan 2007 18:06:00 -0800, Gatsby
wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #9   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 26
Default Fun With That Wages Book Again

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #10   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #11   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 26
Default Fun With That Wages Book Again

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #12   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #13   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,886
Default Fun With That Wages Book Again

Hi Gatsby

=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))

has too many parentheses in the formula
Try
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04,2.54+(G9-127)*0.06))

To combine your other formulae, try
=IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9<=440,"AL"," A1")))

--
Regards

Roger Govier


"Gatsby" wrote in message
...
Not at all, Yacbo. you were a great help to me with my original
question.
It's just I feel I know nothing. I did the online demos from Microsoft
on
Excel. I could understand them and do the practicals. It's like
learning to
drive in a car park; - so easy, and then being put on to a highway
during
rush hour. Totally haven't a clue how to do anything! For example: You
gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the
'0.04',
didn't I? And it wouldn't work. And I don't know what difference I am
making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",)
Works Fine
=IF(G9<=440,"AL",)
Works Fine

But I can't join them together in one formula. I've tried putting in
double
parentethis like in your formula. Can't get it to work. "AO",
"AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I
also
wanted to include in the formula that anything over 440 was to be "A1"
(A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's'
in the
one cell.
At this stage I feel I'd be better writing everything by hand and just
using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way
around.
Kassie has apparently been most helpful to you as has Gord. These
guys are
vastly superior to my humble abilities and perhaps one of them will
weigh in
and answer your question. For my part, I don't quite understand the
current
question, but stand willing to help in any way possible. To that
end, I have
two suggestions: First, do another post being more specific as to
what the
input and output are and why the formulas are not working. And two,
you
might submit your post to the category of Excel"Worksheet Functions"
in
addition to this room. Please don't consider this latter suggestion
as
diversionary-- I just think you might be depriving yourself of a
valuable
resource in the folks that regularly help over in the "Worksheet
Functions"
room in which I have gained many helpful tips. I would like to
stress that
the people who offer help in all these communities are top notch. I
feel
guilty that I help so little and gain so much from people like
Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still
have
problems in other columns with them being filled in all the way
down with
'incorrect' figures until I make an entry. When I make an entry it
corrects
the figure in the adjacent cell to which it refers but the rest,
all the way
down should not be there. It drives me mad. It doesn't happen with
the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on
you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising
now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in
G in order
to get H to complete. If I drag the auto complete down it fills
in the last
entry all down the column, which I don't want to happen. Why
doesn't excel
recognise what I'm at and just fill in column H for me?
Although the above
example only shows four entries, it's the same after seven. The
Autocomplete
box is ticked.
Help anyone? - Gatsby



  #14   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 26
Default Fun With That Wages Book Again

Sure, you can switch ((G9-127)*0.04) with (0.04*(G9-127)) and mathematically,
it makes no difference, which begs the question why do it? But you cant
switch ((G9-127)*0.04) with (G9-127)*0.04)) which is what you have done.
Actually, due to the order of math, you could leave out the outer parenthesis
in my formula and just type (G9-127)*0.04 or 0.04*(G9-127) instead and it
wouldnt matter. I just include additional parenthesis to make it neater.
Your error is coming from the improper placement of the parenthesis.

But lets make this easier by using helper columns. Below, I start with a
raw number in cell G9. For the other cells (H9-K9), the computed number is
shown and the formula that got us there is listed below.

600 0 18.92 30.92 28.38
(raw) (1) (2) (3) (4)

Formulas:

(1) =0*G9
(2) =(G9-127) * 0.04
(3) =127*0.02 + (G9-127)*0.04
(4) =IF(G9<=300,H9,IF(G9<=440,I9,J9))

This method should also work with your SI coding. Using the helper columns
lets you see what is happening. Type these formulas into a blank Excel sheet
and then change the raw number to various samples from your range to see the
result in cell K9.

--
Y


"Gatsby" wrote:

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #15   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 26
Default Fun With That Wages Book Again

Oops, last number in my example (in cell K9) should read: 30.92. Got a
little ahead of myself. Sorry.
--
Y


"Yacbo" wrote:

Sure, you can switch ((G9-127)*0.04) with (0.04*(G9-127)) and mathematically,
it makes no difference, which begs the question why do it? But you cant
switch ((G9-127)*0.04) with (G9-127)*0.04)) which is what you have done.
Actually, due to the order of math, you could leave out the outer parenthesis
in my formula and just type (G9-127)*0.04 or 0.04*(G9-127) instead and it
wouldnt matter. I just include additional parenthesis to make it neater.
Your error is coming from the improper placement of the parenthesis.

But lets make this easier by using helper columns. Below, I start with a
raw number in cell G9. For the other cells (H9-K9), the computed number is
shown and the formula that got us there is listed below.

600 0 18.92 30.92 28.38
(raw) (1) (2) (3) (4)

Formulas:

(1) =0*G9
(2) =(G9-127) * 0.04
(3) =127*0.02 + (G9-127)*0.04
(4) =IF(G9<=300,H9,IF(G9<=440,I9,J9))

This method should also work with your SI coding. Using the helper columns
lets you see what is happening. Type these formulas into a blank Excel sheet
and then change the raw number to various samples from your range to see the
result in cell K9.

--
Y


"Gatsby" wrote:

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #16   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Thanks, Roger. Yes that works perfectly.
I didn't reckon on when an employee is out of work in a particular week and
has no wages. the employee gets an'AO' when it should be zilch.
How can I add in: If (G9<=0,0) into the formula without getting error
messages again?
Thanks again, Roger.

Gatsby (Not The Great)


"Roger Govier" wrote:

Hi Gatsby

=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))

has too many parentheses in the formula
Try
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04,2.54+(G9-127)*0.06))

To combine your other formulae, try
=IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9<=440,"AL"," A1")))

--
Regards

Roger Govier


"Gatsby" wrote in message
...
Not at all, Yacbo. you were a great help to me with my original
question.
It's just I feel I know nothing. I did the online demos from Microsoft
on
Excel. I could understand them and do the practicals. It's like
learning to
drive in a car park; - so easy, and then being put on to a highway
during
rush hour. Totally haven't a clue how to do anything! For example: You
gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the
'0.04',
didn't I? And it wouldn't work. And I don't know what difference I am
making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",)
Works Fine
=IF(G9<=440,"AL",)
Works Fine

But I can't join them together in one formula. I've tried putting in
double
parentethis like in your formula. Can't get it to work. "AO",
"AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I
also
wanted to include in the formula that anything over 440 was to be "A1"
(A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's'
in the
one cell.
At this stage I feel I'd be better writing everything by hand and just
using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way
around.
Kassie has apparently been most helpful to you as has Gord. These
guys are
vastly superior to my humble abilities and perhaps one of them will
weigh in
and answer your question. For my part, I don't quite understand the
current
question, but stand willing to help in any way possible. To that
end, I have
two suggestions: First, do another post being more specific as to
what the
input and output are and why the formulas are not working. And two,
you
might submit your post to the category of Excel"Worksheet Functions"
in
addition to this room. Please don't consider this latter suggestion
as
diversionary-- I just think you might be depriving yourself of a
valuable
resource in the folks that regularly help over in the "Worksheet
Functions"
room in which I have gained many helpful tips. I would like to
stress that
the people who offer help in all these communities are top notch. I
feel
guilty that I help so little and gain so much from people like
Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still
have
problems in other columns with them being filled in all the way
down with
'incorrect' figures until I make an entry. When I make an entry it
corrects
the figure in the adjacent cell to which it refers but the rest,
all the way
down should not be there. It drives me mad. It doesn't happen with
the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on
you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising
now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in
G in order
to get H to complete. If I drag the auto complete down it fills
in the last
entry all down the column, which I don't want to happen. Why
doesn't excel
recognise what I'm at and just fill in column H for me?
Although the above
example only shows four entries, it's the same after seven. The
Autocomplete
box is ticked.
Help anyone? - Gatsby




  #17   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Thanks again, Yacbo.
It took me a while to work out what you were at in this one.
I think there is a typo and you mean 0.06 in I9, not 0.04.
Anyway I see what you mean.
Thanks for explaining it. What's the significance of double and even treble
parentheses?
Gatsby (Not The Great)

"Yacbo" wrote:

Oops, last number in my example (in cell K9) should read: 30.92. Got a
little ahead of myself. Sorry.
--
Y


"Yacbo" wrote:

Sure, you can switch ((G9-127)*0.04) with (0.04*(G9-127)) and mathematically,
it makes no difference, which begs the question why do it? But you cant
switch ((G9-127)*0.04) with (G9-127)*0.04)) which is what you have done.
Actually, due to the order of math, you could leave out the outer parenthesis
in my formula and just type (G9-127)*0.04 or 0.04*(G9-127) instead and it
wouldnt matter. I just include additional parenthesis to make it neater.
Your error is coming from the improper placement of the parenthesis.

But lets make this easier by using helper columns. Below, I start with a
raw number in cell G9. For the other cells (H9-K9), the computed number is
shown and the formula that got us there is listed below.

600 0 18.92 30.92 28.38
(raw) (1) (2) (3) (4)

Formulas:

(1) =0*G9
(2) =(G9-127) * 0.04
(3) =127*0.02 + (G9-127)*0.04
(4) =IF(G9<=300,H9,IF(G9<=440,I9,J9))

This method should also work with your SI coding. Using the helper columns
lets you see what is happening. Type these formulas into a blank Excel sheet
and then change the raw number to various samples from your range to see the
result in cell K9.

--
Y


"Gatsby" wrote:

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

  #18   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,886
Default Fun With That Wages Book Again

Hi

=IF(G9<=0,0,IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9< =440,"AL","A1"))))


--
Regards

Roger Govier


"Gatsby" wrote in message
...
Thanks, Roger. Yes that works perfectly.
I didn't reckon on when an employee is out of work in a particular
week and
has no wages. the employee gets an'AO' when it should be zilch.
How can I add in: If (G9<=0,0) into the formula without getting error
messages again?
Thanks again, Roger.

Gatsby (Not The Great)


"Roger Govier" wrote:

Hi Gatsby

=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))

has too many parentheses in the formula
Try
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04,2.54+(G9-127)*0.06))

To combine your other formulae, try
=IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9<=440,"AL"," A1")))

--
Regards

Roger Govier


"Gatsby" wrote in message
...
Not at all, Yacbo. you were a great help to me with my original
question.
It's just I feel I know nothing. I did the online demos from
Microsoft
on
Excel. I could understand them and do the practicals. It's like
learning to
drive in a car park; - so easy, and then being put on to a highway
during
rush hour. Totally haven't a clue how to do anything! For example:
You
gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the
'0.04',
didn't I? And it wouldn't work. And I don't know what difference I
am
making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",)
Works Fine
=IF(G9<=440,"AL",)
Works Fine

But I can't join them together in one formula. I've tried putting
in
double
parentethis like in your formula. Can't get it to work. "AO",
"AX","AL" are
NOT cell no's, they are references in the Social Insurance system.
I
also
wanted to include in the formula that anything over 440 was to be
"A1"
(A
letter and a figure) but I'm not sure Excel will let me go to 4
'IF's'
in the
one cell.
At this stage I feel I'd be better writing everything by hand and
just
using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my
way
around.
Kassie has apparently been most helpful to you as has Gord. These
guys are
vastly superior to my humble abilities and perhaps one of them
will
weigh in
and answer your question. For my part, I don't quite understand
the
current
question, but stand willing to help in any way possible. To that
end, I have
two suggestions: First, do another post being more specific as to
what the
input and output are and why the formulas are not working. And
two,
you
might submit your post to the category of Excel"Worksheet
Functions"
in
addition to this room. Please don't consider this latter
suggestion
as
diversionary-- I just think you might be depriving yourself of a
valuable
resource in the folks that regularly help over in the "Worksheet
Functions"
room in which I have gained many helpful tips. I would like to
stress that
the people who offer help in all these communities are top notch.
I
feel
guilty that I help so little and gain so much from people like
Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still
have
problems in other columns with them being filled in all the way
down with
'incorrect' figures until I make an entry. When I make an entry
it
corrects
the figure in the adjacent cell to which it refers but the rest,
all the way
down should not be there. It drives me mad. It doesn't happen
with
the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call
on
you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are
cruising
now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry
in
G in order
to get H to complete. If I drag the auto complete down it
fills
in the last
entry all down the column, which I don't want to happen. Why
doesn't excel
recognise what I'm at and just fill in column H for me?
Although the above
example only shows four entries, it's the same after seven.
The
Autocomplete
box is ticked.
Help anyone? - Gatsby






  #19   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

That's great, Roger. I'm noting the 4 end parenteses.
Thanks very much for your help and reply. It is appreciated.

Gatsby (Not The Great)

"Roger Govier" wrote:

Hi

=IF(G9<=0,0,IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9< =440,"AL","A1"))))


--
Regards

Roger Govier


"Gatsby" wrote in message
...
Thanks, Roger. Yes that works perfectly.
I didn't reckon on when an employee is out of work in a particular
week and
has no wages. the employee gets an'AO' when it should be zilch.
How can I add in: If (G9<=0,0) into the formula without getting error
messages again?
Thanks again, Roger.

Gatsby (Not The Great)


"Roger Govier" wrote:

Hi Gatsby

=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
has too many parentheses in the formula
Try
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04,2.54+(G9-127)*0.06))

To combine your other formulae, try
=IF(G9<=300,"AO",IF(G9<=356,"AX",IF(G9<=440,"AL"," A1")))

--
Regards

Roger Govier


"Gatsby" wrote in message
...
Not at all, Yacbo. you were a great help to me with my original
question.
It's just I feel I know nothing. I did the online demos from
Microsoft
on
Excel. I could understand them and do the practicals. It's like
learning to
drive in a car park; - so easy, and then being put on to a highway
during
rush hour. Totally haven't a clue how to do anything! For example:
You
gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the
'0.04',
didn't I? And it wouldn't work. And I don't know what difference I
am
making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",)
Works Fine
=IF(G9<=440,"AL",)
Works Fine

But I can't join them together in one formula. I've tried putting
in
double
parentethis like in your formula. Can't get it to work. "AO",
"AX","AL" are
NOT cell no's, they are references in the Social Insurance system.
I
also
wanted to include in the formula that anything over 440 was to be
"A1"
(A
letter and a figure) but I'm not sure Excel will let me go to 4
'IF's'
in the
one cell.
At this stage I feel I'd be better writing everything by hand and
just
using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my
way
around.
Kassie has apparently been most helpful to you as has Gord. These
guys are
vastly superior to my humble abilities and perhaps one of them
will
weigh in
and answer your question. For my part, I don't quite understand
the
current
question, but stand willing to help in any way possible. To that
end, I have
two suggestions: First, do another post being more specific as to
what the
input and output are and why the formulas are not working. And
two,
you
might submit your post to the category of Excel"Worksheet
Functions"
in
addition to this room. Please don't consider this latter
suggestion
as
diversionary-- I just think you might be depriving yourself of a
valuable
resource in the folks that regularly help over in the "Worksheet
Functions"
room in which I have gained many helpful tips. I would like to
stress that
the people who offer help in all these communities are top notch.
I
feel
guilty that I help so little and gain so much from people like
Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still
have
problems in other columns with them being filled in all the way
down with
'incorrect' figures until I make an entry. When I make an entry
it
corrects
the figure in the adjacent cell to which it refers but the rest,
all the way
down should not be there. It drives me mad. It doesn't happen
with
the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call
on
you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are
cruising
now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry
in
G in order
to get H to complete. If I drag the auto complete down it
fills
in the last
entry all down the column, which I don't want to happen. Why
doesn't excel
recognise what I'm at and just fill in column H for me?
Although the above
example only shows four entries, it's the same after seven.
The
Autocomplete
box is ticked.
Help anyone? - Gatsby







  #20   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 26
Default Fun With That Wages Book Again

Yea, you got me on the typo. Sorry.

As for the parenthesis, sometimes they are necessary and sometimes not. For
instance, the expression A + B * C gives the exact same result as A + (B * C)
and the parenthesis are superfluous. But they do serve the purpose of
allowing me to follow what I am trying to accomplish. For example, a change
of the placement of the parenthesis to (A + B) * C produces an entirely
different result. In this case, the parenthesis are important because Im
telling Excel to add A to B first and then multiply by C, which it would not
normally do. This is in stark contrast to the first example in which I am
telling Excel to multiply B and C and then add the product to A which is what
Excel would normally do with or without the parenthesis. In both examples, I
am telling Excel explicitly what I want done without relying on its rules of
math order. As long as you are well versed in mathematical protocol, you may
feel free to forgo the extra parenthesis, but do be careful.

--
Y


"Gatsby" wrote:

Thanks again, Yacbo.
It took me a while to work out what you were at in this one.
I think there is a typo and you mean 0.06 in I9, not 0.04.
Anyway I see what you mean.
Thanks for explaining it. What's the significance of double and even treble
parentheses?
Gatsby (Not The Great)

"Yacbo" wrote:

Oops, last number in my example (in cell K9) should read: 30.92. Got a
little ahead of myself. Sorry.
--
Y


"Yacbo" wrote:

Sure, you can switch ((G9-127)*0.04) with (0.04*(G9-127)) and mathematically,
it makes no difference, which begs the question why do it? But you cant
switch ((G9-127)*0.04) with (G9-127)*0.04)) which is what you have done.
Actually, due to the order of math, you could leave out the outer parenthesis
in my formula and just type (G9-127)*0.04 or 0.04*(G9-127) instead and it
wouldnt matter. I just include additional parenthesis to make it neater.
Your error is coming from the improper placement of the parenthesis.

But lets make this easier by using helper columns. Below, I start with a
raw number in cell G9. For the other cells (H9-K9), the computed number is
shown and the formula that got us there is listed below.

600 0 18.92 30.92 28.38
(raw) (1) (2) (3) (4)

Formulas:

(1) =0*G9
(2) =(G9-127) * 0.04
(3) =127*0.02 + (G9-127)*0.04
(4) =IF(G9<=300,H9,IF(G9<=440,I9,J9))

This method should also work with your SI coding. Using the helper columns
lets you see what is happening. Type these formulas into a blank Excel sheet
and then change the raw number to various samples from your range to see the
result in cell K9.

--
Y


"Gatsby" wrote:

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby



  #21   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 42
Default Fun With That Wages Book Again

Thanks Yacbo! Your reply is appreciated,

Gatsby (Not The Great)

"Yacbo" wrote:

Yea, you got me on the typo. Sorry.

As for the parenthesis, sometimes they are necessary and sometimes not. For
instance, the expression A + B * C gives the exact same result as A + (B * C)
and the parenthesis are superfluous. But they do serve the purpose of
allowing me to follow what I am trying to accomplish. For example, a change
of the placement of the parenthesis to (A + B) * C produces an entirely
different result. In this case, the parenthesis are important because Im
telling Excel to add A to B first and then multiply by C, which it would not
normally do. This is in stark contrast to the first example in which I am
telling Excel to multiply B and C and then add the product to A which is what
Excel would normally do with or without the parenthesis. In both examples, I
am telling Excel explicitly what I want done without relying on its rules of
math order. As long as you are well versed in mathematical protocol, you may
feel free to forgo the extra parenthesis, but do be careful.

--
Y


"Gatsby" wrote:

Thanks again, Yacbo.
It took me a while to work out what you were at in this one.
I think there is a typo and you mean 0.06 in I9, not 0.04.
Anyway I see what you mean.
Thanks for explaining it. What's the significance of double and even treble
parentheses?
Gatsby (Not The Great)

"Yacbo" wrote:

Oops, last number in my example (in cell K9) should read: 30.92. Got a
little ahead of myself. Sorry.
--
Y


"Yacbo" wrote:

Sure, you can switch ((G9-127)*0.04) with (0.04*(G9-127)) and mathematically,
it makes no difference, which begs the question why do it? But you cant
switch ((G9-127)*0.04) with (G9-127)*0.04)) which is what you have done.
Actually, due to the order of math, you could leave out the outer parenthesis
in my formula and just type (G9-127)*0.04 or 0.04*(G9-127) instead and it
wouldnt matter. I just include additional parenthesis to make it neater.
Your error is coming from the improper placement of the parenthesis.

But lets make this easier by using helper columns. Below, I start with a
raw number in cell G9. For the other cells (H9-K9), the computed number is
shown and the formula that got us there is listed below.

600 0 18.92 30.92 28.38
(raw) (1) (2) (3) (4)

Formulas:

(1) =0*G9
(2) =(G9-127) * 0.04
(3) =127*0.02 + (G9-127)*0.04
(4) =IF(G9<=300,H9,IF(G9<=440,I9,J9))

This method should also work with your SI coding. Using the helper columns
lets you see what is happening. Type these formulas into a blank Excel sheet
and then change the raw number to various samples from your range to see the
result in cell K9.

--
Y


"Gatsby" wrote:

Not at all, Yacbo. you were a great help to me with my original question.
It's just I feel I know nothing. I did the online demos from Microsoft on
Excel. I could understand them and do the practicals. It's like learning to
drive in a car park; - so easy, and then being put on to a highway during
rush hour. Totally haven't a clue how to do anything! For example: You gave
me the following formula and it works great for me:
=IF(G9<=300,0,IF(G9<=440,(0.04*(G9-127)),(2.54+(G9-127)*0.06))).
Then, Yacbo, I was putting it in somewhere else and I moved the '0.04',
didn't I? And it wouldn't work. And I don't know what difference I am making
to how the formula works:
=IF(G9<=300,0,IF(G9<=440,(G9-127)*0.04)),(2.54+(G9-127)*0.06)))
It won't work that way. I get the error message.

Then I have the following: =IF(G9<=300,"AO",) Works Fine
=IF(G9<=356,"AX",) Works Fine
=IF(G9<=440,"AL",) Works Fine

But I can't join them together in one formula. I've tried putting in double
parentethis like in your formula. Can't get it to work. "AO", "AX","AL" are
NOT cell no's, they are references in the Social Insurance system. I also
wanted to include in the formula that anything over 440 was to be "A1" (A
letter and a figure) but I'm not sure Excel will let me go to 4 'IF's' in the
one cell.
At this stage I feel I'd be better writing everything by hand and just using
a calculator, such is my frustration.
Gatsby.


"Yacbo" wrote:

To be honest, I'm kind of new to this forum and just learning my way around.
Kassie has apparently been most helpful to you as has Gord. These guys are
vastly superior to my humble abilities and perhaps one of them will weigh in
and answer your question. For my part, I don't quite understand the current
question, but stand willing to help in any way possible. To that end, I have
two suggestions: First, do another post being more specific as to what the
input and output are and why the formulas are not working. And two, you
might submit your post to the category of Excel"Worksheet Functions" in
addition to this room. Please don't consider this latter suggestion as
diversionary-- I just think you might be depriving yourself of a valuable
resource in the folks that regularly help over in the "Worksheet Functions"
room in which I have gained many helpful tips. I would like to stress that
the people who offer help in all these communities are top notch. I feel
guilty that I help so little and gain so much from people like Kassie, Gord,
and many others! Thanks to all.
--
Y


"Gatsby" wrote:

Thanks, Yacbo!
I still get stuck. Kassie has been a great help, though. I still have
problems in other columns with them being filled in all the way down with
'incorrect' figures until I make an entry. When I make an entry it corrects
the figure in the adjacent cell to which it refers but the rest, all the way
down should not be there. It drives me mad. It doesn't happen with the
Formula Kassie gave me, though (=IF(G9="","",H10+G9). Can I call on you guys
again or will I drive you all mad?
Gatsby.

"Yacbo" wrote:

Sorry I missed all the fun, but it sounds like you are cruising now!
--
Y


"Gatsby" wrote:

G H
450.00 450.00 (G9)
425.00 875.00 (G10+H9)
430.00 1320.00 (G11+H10)
422.00 1742.00 (G12+H11)
1742.00
1742.00
I have to type in the formula in H each time I make an entry in G in order
to get H to complete. If I drag the auto complete down it fills in the last
entry all down the column, which I don't want to happen. Why doesn't excel
recognise what I'm at and just fill in column H for me? Although the above
example only shows four entries, it's the same after seven. The Autocomplete
box is ticked.
Help anyone? - Gatsby

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A Formula For A Wages Book Gatsby Setting up and Configuration of Excel 3 January 7th 07 01:25 PM
linking multiple books (source.xls) to a single book (destination. mday Excel Discussion (Misc queries) 4 August 25th 06 04:31 AM
Could you recommend an Excel book? Evgeny Excel Discussion (Misc queries) 1 February 8th 06 06:05 AM
book selection when using move or copy tab in excel Morninglight67 Excel Worksheet Functions 0 October 28th 05 09:15 PM
Work book formulas Jessica Excel Discussion (Misc queries) 1 June 9th 05 10:51 AM


All times are GMT +1. The time now is 08:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"