If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 I’m 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 can’t 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 wouldn’t matter. I just include additional parenthesis to make it neater. Your error is coming from the improper placement of the parenthesis. But let’s 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 |
Thread Tools | |
Display Modes | |
|
|