help make a formula adding ingredient unit

[choy96]

can you help me make another formula that can add together all balance.

1ca + 1ca + 1ca + 11b 3p 1cu = 3ca 11b 3p 1cu

1ca = 16b
1b = 5p
1p = 3cu

ca=carton, b=box, p=pump, cu=cup


thanks

[FlameRetired]

I am confused by the instructions versus the workbook sections. Do you want the formula to make what is in cell K15

3ca 11b 3p 1cu

or do you want the result of the math which is 659 cu?

Edit That should be 1649 cu!!! I had the lookup table upside down.

[dosydos]

well if you want your result to be "3ca 11b 3p 1cu"
then you can use this array formula. You need to enter this formula with CTRL+SHIFT+ENTER. If done so correctly { } will be added around your formula.

Please Login or Register  to view this content.

[choy96]

FlameRetired , yes, must match to the cu. 3 carton 11 box 3 pump 1 cup equal to 895 cup.

3 carton = 48 box , 48+11= 59 box
59 box = 295 pump , 295 + 3 = 298 pump
298 pump = 894 cup , 894 + 1 = 895 pump (total)

895 pump , it will show 3ca 11b 3p 1cu

thanks

[tim201110]

different approach
Excel works with numbers with respect

Please Login or Register  to view this content.

[choy96]

Thanks tim201110, your formula good can add together all but not suitable because at the end table i want put a formula to check total ingredient must be matched.
Hope can someone help me please. thanks very much.

[JeteMc]

If you remove the space in "cu " at the end of dosydos' array entered formula then it yields the same answer as that which is being shown in cell K15 of the file attached to post #6 (L15 displays 'Yes').

[choy96]

Dear JeteMc, I dont understand what your means, can you make a example for me ？
Thanks.

[JeteMc]

Here is the file attached to post #6 with dosydos' array entered formula applied.
Let us know if you have any questions.

[choy96]

thanks JeteMc
Result 4p + 4p + 3ca 10b 0p 1cu it show 3ca 10b 8p 1cu, it equal to 3ca 11b 3p 1cu . can it show 3ca 11b 3p 1cu ?

Please help.
Thanks very much.

[JeteMc]

I see what you mean. I'll look closer, but as of now I don't know how that could be done.

[JeteMc]

This proposal employs a number of helper columns which may be moved and/or hidden for aesthetic purposes.
The first four helper columns parses dosydos formula by unit i.e. for ca: =SUM(IF(ISERROR(SEARCH("*ca",E15:J15)),0,VALUE(LEFT(E15:J15,SEARCH("ca",E15:J15)-1))))
The rest of the help columns alternately find the MOD of each unit with respect to the next larger unit and SUMs the new value for the larger unit (if applicable).
I.E. for MOD of p's: =MOD(S15,5)
I.E. for SUM of b's: =IF(T15<S15,SUM(O15,INT(S15-T15)/5),O15)
The Total cell is populated using: =W15&"ca "&V15&"b "&T15&"p "&R15&"cu"
Let us know if you have any questions.

[choy96]

Thanks again JeteMc .

when i copy your formula on K15 to K16 , why the formula cannot work ?

Please help.
Thanks very much.

[JeteMc]

Select N15:W15 and then drag the fill handle down to W16.
Let us know if you have any questions.

[choy96]

Thanks JeteMc.
But the adding result on some value is wrong
1ca 11b 1p + 3b + 3b = 2ca 1b 1p 0cu
but result is 4.2ca 1b 1p 0cu, is wrong.
can you fix for me.

thanks very much

[JeteMc]

Please make the following two changes:
For cell N12 and down**: =SUM(IF(ISERROR(SEARCH("ca",E12:J12)),0,VALUE(LEFT(E12:J12,SEARCH("ca",E12:J12)-1))))
For cell W12 and down: =IF(V12<U12,SUM(N12,INT(U12-V12)/16),N12)
**This is an array formulas which are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.