Divided into Specific Numbers

[seanyeap]

Hi All,
I need help to work out a formula to achieve a specific breakdown when 2 values are divided. I apologise for the vague explanation, please refer to my attachment for better understanding. Thanks for the time.

[shg]

How about just a function?

In F2 and copy down, ="8 * " & INT(A2/8) & CHOOSE( 8 * (A2/8 - INT(A2/8)) + 1, "", "+ 1", "+ 2", "+ 2 + 1", "+ 4", "+ 4 + 1", "+ 4 + 2", "+ 4 + 2 + 1")

[ptm0412]

It should be:
A1 = 57 (or any)
B1 = 8 (or any)

Please Login or Register  to view this content.

It works already, I've tested.

[seanyeap]

Hi Masters,
I am more comfortable with this

Please Login or Register  to view this content.

I am really appreciative and thankful for t he great help.

[shg]

The formula is written for a divisor of 8, considering the limited options of expressing the residual (+1, +2, ... +4+2+1). A VBA function could be generalized for any divisor.

[seanyeap]

Hi shg,
Now with the formula you gave below I am able to achieve the answer I want in the cell B25

"("&INT(A2/B2)&" X "&B2&")"&CHOOSE(B2*(A2/B2-INT(A2/B2))+1,""," + 1"," + 2"," + 2 + 1"," + 4"," + 4 + 1"," + 4 + 2"," + 4 + 2 + 1")

how do I set IF worksheet function when B24 is not "8" or "4"? I want it returns to be "empty" in B25. Thanks a lot.

Pls take a look at the attached file

[seanyeap]

Hi,
Can someone please help to fine tune? Thanks a million

[seanyeap]

Can someone please share some tips for me? Thanks a lot

[Portuga]

try this:

=if(and(B24<>8,B24<>4),"",

and then your formula

[seanyeap]

Finally I got it settled,thanks a million

[seanyeap]

Hi,
I have got the result in B25, I need to convert it to the following:-

(7 X 8) + 4 + 2 + 1, convert into
(7 X 8) is sheet 1 to sheet 7
+ 4 is sheet 8
+ 2 is sheet 9
+ 1 is sheet 10

(6 X 8) + 4 + 2 + 1, convert into
(6 X 8) is sheet 1 to sheet 6
+ 4 is sheet 7
+ 2 is sheet 8
+ 1 is sheet 9

(5 X 8) + 4 + 2, convert into
(5 X 8) is sheet 1 to sheet 5
+ 4 is sheet 6
+ 2 is sheet 7

I humbly seek advice and help. Please check my attachment. Thanks a lot

[rylo]

Hi

1) B25:

=IF(AND(B24<>8,B24<>4),"","("&INT(B23/B24)&" X "&B24&")"&CHOOSE(B24*(B23/B24-INT(B23/B24))+1,""," + 1"," + 2"," + 2 + 1"," + 4"," + 4 + 1"," + 4 + 2"," + 4 + 2 + 1"))

2) I don't follow what you mean by

(7 X 8) is sheet 1 to sheet 7

. Do you want to convert the string (7 X 8) to be sheet 1-7? And where do you want to put the output? It seems to be overlapping with the next lot of items.

rylo

[seanyeap]

Hi,
If B25 is (7 X 8) + 4 + 2 + 1
I want C37 returns as "sheet 1 to sheet 7"
C38 as "sheet 8"
C39 as "sheet 9"
C40 as "sheet 10"

If B25 is (6 X 8) + 4 + 2 + 1
I want C37 returns as "1 to sheet 6"
C38 as "sheet 7"
C39 as "sheet 8"
C40 as "sheet 9"

If B25 is (5 X 8) + 4 + 2
I want C37 returns as "1 to sheet 5"
C38 as "sheet 6"
C39 as "sheet 7"

Apologise for the poor explanation

[rylo]

Hi

C37: ="sheet 1 to sheet " & MID(A35,2,1)
C38: ="sheet " & MID(A35,2,1)+1
C39: ="sheet " & MID(A35,2,1)+2
C40: =IF(MID(A35,2,1) = 5,"","sheet " & MID(A35,2,1)+3)

HTH

rylo

[seanyeap]

Wow,thanks for the solution.

[seanyeap]

Hi rylo,
With your MID Worksheet Function, some results are ok but some arent. I need you to fine tune, thanks a lot.

If B25 is (7 X 8) + 4 + 2 + 1
C37:sheet 1 to sheet 7 ......result is correct
C38:sheet 8 .............result is correct
C39:sheet 9 .............result is correct
C40:sheet 10 ...........result is correct


If B25 is (6 X 8) + 4 + 2
C37:sheet 1 to sheet 6 ......result is correct
C38:sheet 7 ......result is correct
C39:sheet 8 ......result is correct
C40:sheet 9 ......result is wrong as this line is supposed to be empty string


If B25 is (5 X 8) + 4
C37:sheet 1 to sheet 5 ......result is correct
C38:sheet 6 ......result is correct
C39:sheet 7 ......result is wrong as this line is supposed to be empty string
C40:sheet 8 ......result is wrong as this line is supposed to be empty string

[rylo]

Hi

OK try

C37: ="sheet 1 to sheet " & MID(A35,2,1)
C38: ="sheet " & MID(A35,2,1)+1
C39: =IF(MID(A35,2,1) = "5","","sheet " & MID(A35,2,1)+2)
C40: =IF(OR(MID(A35,2,1) = "5",MID(A35,2,1) = "6"),"","sheet " & MID(A35,2,1)+3)

rylo

[shg]

Hey, sean,

Could you share the purpose of all this?

[seanyeap]

Hi shg,
It is a optimazation program where material is utilised economically into smaller sections.

Hi rylo,
Please check the attachment for better understanding as I have made a few scenarios. Thanks for the time.

[rylo]

Hi

See how this goes. I've put in the scenarios and the responses generated for each.

rylo

[seanyeap]

Hi rylo,
I have tested all scenarios and there are some positive and negative responses. Pls see my attachment. Thanks again

[rylo]

Hi


Had a quick look at the attachment, and you keep coming up with different scenarios.

Can you please make sure you have covered all your possible situations, and worked out how they should be covered.

I'll have another look when you have confirmed that all your scenarios have been covered.

rylo

[seanyeap]

Thousand apologies. I will make sure all scenarios are covered

[seanyeap]

Sorry for all the confusion and inconvenince I have caused. This time, I have included all possibilities and situations. Hoping that I will have the final solution. Thanks a million

[rylo]

Hi

I can't see any easy consistent way to cover the 0x and 1x possibilities, so I think the easiest solution would be to build a reference table and use VLOOKUP to bring back the relevant outputs.

rylo

[seanyeap]

HI rylo,
Can I use the Function MID and LEN to cover the 0x and 1x possibilities? As I am even worse in VLOOKUP.Pls adv

[rylo]

It may well be possible, but I couldn't think of any algorithmic approach to cover the scenarios - at least not easy ones.

That's why I suggested the VLOOKUP approach. If you build a list of all the possible options, and what the output should be for the first string, second string etc, then VLOOKUP will be much easier to implement.

I'm still mulling, and if I can think of a simplisitic algorithm, then I'll put it up, but at this stage, I'd go for the VLOOKUP approach.

rylo

[shg]

I agree with Rylo. You last post seems to provide all you need for the lookup table, and it eliminates all the complex formulas:

Please Login or Register  to view this content.

[seanyeap]

Hi rylo & shg,
As advised by you guys, I have somehow created a matrix for Vlookup. It is still being processed, however I need some tips and help on the MID Function. Pls refer to my attachment. Thanks

[shg]

I'll leave the formula approach to you and rylo. The lookup table rylo suggested is straightforward and simple.

[rylo]

Hi

Easiest way here is to find the first space, and use that to determine the length.

Using your latest file, try this

=MID(F14,2,SEARCH(" ",F14)-2)

rylo

[seanyeap]

Hi rylo,
The Search Function has made my headache away. Pls check my attachment.