Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unsure about which functions to use
My problem sounds simple if you say it, however I am very new to Excel and
can't figure out how to do it. I have numbers 1 to 18 in a column A. Column B can be any number dependent on another sheet. Column C should be column B divided by a number which is dependent on the number in the A cell it is adjacent to. eg if adjacent to 1 in col A then divide by 1.5, if adjacent to 2 in col A then divide by 1.5, if adjacent to 3 in col A then divide by 1.75. This continues up to number 18 in col A and the adjacent in col B being divided by 3.5 to give the result in col C. The other problem is that 1 to 18 can go back to 1 at any point before actually reaching 18. A B C 1 x x/1.5 2 x x/1.5 3 x x/1.75 4 x x/1.75 5 x x/2.0 etc but can go back to this at any point 1 x x/1.5 any help or suggestions would be really appreciated, thank you, Rich |
#2
|
|||
|
|||
Here's one way:
=IF(AND(A1=1,A1<=18,INT(A1)=A1),B1/((10+ROUNDUP(A1/2,0)*2)/8),"error") HTH Jason Atlanta, GA "Rich" wrote: My problem sounds simple if you say it, however I am very new to Excel and can't figure out how to do it. I have numbers 1 to 18 in a column A. Column B can be any number dependent on another sheet. Column C should be column B divided by a number which is dependent on the number in the A cell it is adjacent to. eg if adjacent to 1 in col A then divide by 1.5, if adjacent to 2 in col A then divide by 1.5, if adjacent to 3 in col A then divide by 1.75. This continues up to number 18 in col A and the adjacent in col B being divided by 3.5 to give the result in col C. The other problem is that 1 to 18 can go back to 1 at any point before actually reaching 18. A B C 1 x x/1.5 2 x x/1.5 3 x x/1.75 4 x x/1.75 5 x x/2.0 etc but can go back to this at any point 1 x x/1.5 any help or suggestions would be really appreciated, thank you, Rich |
#3
|
|||
|
|||
Assuming data starts in row 1, In C1 put
=B1/(0.25*(5+(CEILING(A1/2,1)))) and copy down as far as needed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Rich" wrote in message ... My problem sounds simple if you say it, however I am very new to Excel and can't figure out how to do it. I have numbers 1 to 18 in a column A. Column B can be any number dependent on another sheet. Column C should be column B divided by a number which is dependent on the number in the A cell it is adjacent to. eg if adjacent to 1 in col A then divide by 1.5, if adjacent to 2 in col A then divide by 1.5, if adjacent to 3 in col A then divide by 1.75. This continues up to number 18 in col A and the adjacent in col B being divided by 3.5 to give the result in col C. The other problem is that 1 to 18 can go back to 1 at any point before actually reaching 18. A B C 1 x x/1.5 2 x x/1.5 3 x x/1.75 4 x x/1.75 5 x x/2.0 etc but can go back to this at any point 1 x x/1.5 any help or suggestions would be really appreciated, thank you, Rich |
#4
|
|||
|
|||
On Thu, 21 Apr 2005 17:55:31 +0000 (UTC), "Rich"
wrote: My problem sounds simple if you say it, however I am very new to Excel and can't figure out how to do it. I have numbers 1 to 18 in a column A. Column B can be any number dependent on another sheet. Column C should be column B divided by a number which is dependent on the number in the A cell it is adjacent to. eg if adjacent to 1 in col A then divide by 1.5, if adjacent to 2 in col A then divide by 1.5, if adjacent to 3 in col A then divide by 1.75. This continues up to number 18 in col A and the adjacent in col B being divided by 3.5 to give the result in col C. The other problem is that 1 to 18 can go back to 1 at any point before actually reaching 18. A B C 1 x x/1.5 2 x x/1.5 3 x x/1.75 4 x x/1.75 5 x x/2.0 etc but can go back to this at any point 1 x x/1.5 any help or suggestions would be really appreciated, thank you, Rich Set up a table in a separate area. Column D numbers 1 through 18, Column E the corresponding multiplier. Actually, this can go on a separate sheet if you want. In column C of your data enter =vlookup(A2,$D$1:$E$18,2,FALSE)*B2 copy down. With this approach, you can change the multipliers in the table and all of your results will change accordingly without having to alter the formula. As with any data, back it up and think carefully about retaining historical results before changing the table. Don S |
#5
|
|||
|
|||
Slightly fewer characters
=B1/((5+(CEILING(A1/2,1)))/4) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ken Wright" wrote in message ... Assuming data starts in row 1, In C1 put =B1/(0.25*(5+(CEILING(A1/2,1)))) and copy down as far as needed -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Rich" wrote in message ... My problem sounds simple if you say it, however I am very new to Excel and can't figure out how to do it. I have numbers 1 to 18 in a column A. Column B can be any number dependent on another sheet. Column C should be column B divided by a number which is dependent on the number in the A cell it is adjacent to. eg if adjacent to 1 in col A then divide by 1.5, if adjacent to 2 in col A then divide by 1.5, if adjacent to 3 in col A then divide by 1.75. This continues up to number 18 in col A and the adjacent in col B being divided by 3.5 to give the result in col C. The other problem is that 1 to 18 can go back to 1 at any point before actually reaching 18. A B C 1 x x/1.5 2 x x/1.5 3 x x/1.75 4 x x/1.75 5 x x/2.0 etc but can go back to this at any point 1 x x/1.5 any help or suggestions would be really appreciated, thank you, Rich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
How to load Engineering Functions into the Fx function wizard? | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |