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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>