Function to calculate based on a portions of a number

[Guest]

How would I write a formula to produce various results in differant cells
based on a portion of a number in another cell. Excel 2003

Cell A contains a variable number.
Cell B also containes a variable number.
I need to calculate:
Cell C results need to be based on 1/3rd of Cell A x Cell B, if Cell A is
evenly divided by 3. This part is not a problem (B/(A/3). If Cell A can't be
evenly divided than: Cell A results need to be (B/(A/3) + 100% of cell B if
the remainder is "1" or 50% of Cell B if the remainder is "2" .

Than comes Cell D: This result needs to be (B/(A/3) + 0% if remainder is "1"
and 50% of cell B if remained is "2"

Than comes cell C: This result needs to be (B/(A/3) only on the hole number
result of A/3.

 

[Trevor Shuttleworth]

Not sure I quite follow but, if I have understood:

=IF(A1/3=INT(A1/3), (A1/3)*B1, IF(A1/3-INT(A1/3)=1, (A1/3)*B1+B1,
(A1/3)*B1+B1/2))

A1/3 = Integer (A1/3) if A1 is divisible by 3

A1/3 - Integer (A1/3) gives the remainder = 1 or 2

So, the first part calculates a value based on A1 being divisible by 3.

The second part works out the value dependent on the remainder

I'll leave you to work out the Column D values; should be a similar process

Regards

Trevor

 

[Trevor Shuttleworth]

Not sure I quite follow but, if I have understood:

=IF(A1/3=INT(A1/3), (A1/3)*B1, IF(A1/3-INT(A1/3)=1, (A1/3)*B1+B1,
(A1/3)*B1+B1/2))

A1/3 = Integer (A1/3) if A1 is divisible by 3

A1/3 - Integer (A1/3) gives the remainder = 1 or 2

So, the first part calculates a value based on A1 being divisible by 3.

The second part works out the value dependent on the remainder

I'll leave you to work out the Column D values; should be a similar process

Regards

Trevor

 

[Guest]

Trevor: Thanks for the reply but it does not seem to quite work, Maybe if I
include this chart to show what results I am tring to achive:
Column A = input quantity varies and will be whole numbers only
Column B = input quantity varies and will be whole numbers only (I used 100
in this example for ease of detail)
Columns C,D,E = Desired results based on Qty in A & B

A B C D E
1 100 100 0 0
2 100 50 50 0
3 100 33 33 33
4 100 133 33 33
5 100 83 83 33
6 100 66 66 66
7 100 166 66 66
8 100 116 116 66
17 380 ? ? ?
The last entry (17 and 380) is an example of what could be.

 

[Guest]

Trevor: Thanks for the reply but it does not seem to quite work, Maybe if I
include this chart to show what results I am tring to achive:
Column A = input quantity varies and will be whole numbers only
Column B = input quantity varies and will be whole numbers only (I used 100
in this example for ease of detail)
Columns C,D,E = Desired results based on Qty in A & B

A B C D E
1 100 100 0 0
2 100 50 50 0
3 100 33 33 33
4 100 133 33 33
5 100 83 83 33
6 100 66 66 66
7 100 166 66 66
8 100 116 116 66
17 380 ? ? ?
The last entry (17 and 380) is an example of what could be.

 

[Pete_UK]

I don't see how you get these values from your definition of the
problem in your first posting. I entered the values in columns A and B
and tried this formula:

=IF(MOD(A1,3)=0,B1/A1*3,B1/A1*3+B1/MOD(A1,3))

but this gave me the following values in column C:

400
200
100
175
110
50
142.8571429
87.5

Take the first case: A1 is not divisible by 3 (has a remainder of 1),
and you say in this case:

Cell A results need to be (B/(A/3) + 100% of cell B if the remainder is "1"

I think you mean Cell C (never mind) and if we work this through with
A1=1, you have B1*3 + B1, giving 400.

Can you either revise your table values or re-state the problem
accurately.

Hope this helps.

Pete

 

[Pete_UK]

I don't see how you get these values from your definition of the
problem in your first posting. I entered the values in columns A and B
and tried this formula:

=IF(MOD(A1,3)=0,B1/A1*3,B1/A1*3+B1/MOD(A1,3))

but this gave me the following values in column C:

400
200
100
175
110
50
142.8571429
87.5

Take the first case: A1 is not divisible by 3 (has a remainder of 1),
and you say in this case:

Cell A results need to be (B/(A/3) + 100% of cell B if the remainder is "1"

I think you mean Cell C (never mind) and if we work this through with
A1=1, you have B1*3 + B1, giving 400.

Can you either revise your table values or re-state the problem
accurately.

Hope this helps.

Pete

 

[Guest]

Lets see if I can explain this better.
Condition 1) If column A = 1 than 100% of column B goes to Column C,
Condition 2) If column A = 2 than 50% of column B goes to columns C & D,
Condition 3) If column A = 3 than 33% of colmun B goes to columns C,D & E
Condition 4) if column A is greater than 3, than or every multiple of (3) in
column A I need to meet condition 3 plus the remainder of column A after
dividing by 3 needs to meet conditions 2 or 3.

Does this make sense? (Clear as chocolate milk)

 

[Guest]

Lets see if I can explain this better.
Condition 1) If column A = 1 than 100% of column B goes to Column C,
Condition 2) If column A = 2 than 50% of column B goes to columns C & D,
Condition 3) If column A = 3 than 33% of colmun B goes to columns C,D & E
Condition 4) if column A is greater than 3, than or every multiple of (3) in
column A I need to meet condition 3 plus the remainder of column A after
dividing by 3 needs to meet conditions 2 or 3.

Does this make sense? (Clear as chocolate milk)

 

[Trevor Shuttleworth]

Sorry, can't get my head round this ... tried and tried but no good

 

[Trevor Shuttleworth]

Sorry, can't get my head round this ... tried and tried but no good

 

[Guest]

Gentlemen: Thank you for your assistance. Youe suggestion did help me create
a workaround formula (see below FYI). It is a bit larger than I was hoping
for but it does work. Now I just need to work out the negitive number aspects.
Thanks again

=IF(BL22=1,BL23,IF(BL22=2,BL23/2,IF(BL22=3,BL23/3,IF(AND(BL22>3,(BL22-INT((BL22/3))*3)=1),BL23,IF(AND(BL22>3,(BL22-INT((BL22/3))*3)=2),BL23/2,0))+BB38)))