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

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

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

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

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