Need your tips..

[Manster]

Hi..I'm a beginner in excel and I need solution about my task. Anyone know how to breakdown the value using excel?


Example:


100.1 = 50+50+0.1


Situation is I want to create formula when I enter the value "100.1" for example , automatically its breakdown the values like "50+50+0.1".


TQ

 

[dan_l]

Have a look at the solver.

 

[fred]

So manster, it's like this. let's set up the worksheet

A1: 100.1 (or any number you want)

A2: 50 (or another whole number of your choice)

A3: blank

C3: A1-A2*3 (this is also the cell displaying the result.


In solver, you specify:

Set Target Cell: $C$3

Equal to: Min

By Changing cells: $A$3

Subject to the constraints:

$A$3 = interger

$A$3 >= 0

$C$3 >= 0


click on "solve" button.

 

[SirJB7]

Hi, Manster!


It looks like an old banking problem about how many notes and of what denomination you need for paying a given amount.


If so, you can build something like this:

[pre]

Amount	123,45	Notes	Denomination
23,45	1	100
23,45	0	50
3,45	1	20
3,45	0	10
3,45	0	5
1,45	1	2
0,45	1	1
0,2	1	0,25
0	20	0,01

[/pre]
In B2 type and copy down: =REDONDEAR(B1-C2*D2;2) -----> in english: =ROUND(B1-C2*D2,2)

In C2 type and copy down: =ENTERO(B1/D2) -----> in english: =INT(B1/D2)


If it's not that alike, and you want al algebraic expression or other goal, please advise.

Regards!

 

[Manster]

Hi Fred & SirJB7!


Thanks for your advises. Some of your tips can gain idea for me to solve my problem. Thank you so much.


Regards!

 

[Manster]

Hi SirJB7 and all members!


I already follow SirJB7 tips. But, I have a new problem. Hopefully all members can help.


Length Knife Qty Used Knife Size(Follow knife spec and its fix)

210.025

10.025 4 50--------OK(50*4=200)

10.025 0 40

10.025 0 30

10.025 0 20

10.025 0 10.05

10.025 0 10.04

10.025 0 10.03

0.005 1 10.02-------Not OK

0.005 0 10.01

0.005 0 10.025----Why it not choose this value?

0.005 0 10.015

0.005 0 10

0.005 0 9

0.005 0 8

0.005 0 7

0.005 0 6

0.005 0 5

0.005 0 4

0.005 0 3

0.005 0 2

0.005 0 1.9

0.005 0 1.8

0.005 0 1.7

0.005 0 1.6

0.005 0 1.5

0.005 0 1.4

0.005 0 1.3

0.005 0 1.2

0.005 0 1.1

0.005 0 1

0.005 0 0.98

0.005 0 0.96

0.005 0 0.94

0.005 0 0.9

0.005-------------------END(actually it should become "zero")


Questions:

1.From data above, how does this example length can be matched to all the knife sizes and final become '0'?


2. Example: 162.06 Length

Solution(follow the knife spec above)


162.06=(0.96*1)+(1.1*1)+(50*3)+(10*1)---start from lowest to largest is easy to understand this case


3.As I know, Int function is round the number to the 'nearest integer'. If any formula/function that can be round the numbers to the exact/actual values?


Thanks & Regards!


*Many times i edit and reline that table above to become better,unfortunately it become the table not arrange like above!Really sorry,hope you all understand my table above

 

[SirJB7]

Hi, Manster!


When I posted the formulas I assume that the individual values (notes) where the specified, and I rounded it to two decimals just for avoiding nearly zero errors in last value.


Talking about knives, just please write down all individual knife sizes, and the accuracy in decimals for the tested values, so as to adapt the previous formulas.


About your questions:

1) isn't prepared for 3 decimal places (read previous paraghraph)

2) need the standard sizes (idem)

3) as far as I know, there's no function that rounds to their exact/actual values, because in that case it wouldn't exist any rounding: the problem arises on how Excel stores floating point (that's to say non-integers numbers), and as it does in exponential format +/- x.yyyyyyyyy E+/-nnn (X mantissa, y decimal places, nnn power of ten), sometimes instead of number (for example) 8, it results in a 8.00000001 E 00, that's not exactly 8... and that's were the number of decimal places play: in notes, 2, in your case, let me know.


Regards!

 

[Manster]

Hi...


How can I create a function/formula in this case:


If either values of 35.54 or 45.54 show up, it should be subtract to 0.94.


"=If(or(35.54,45.54),35.54-0.94,35.54-0.94)"

I realize that this function is incorrect,so how can I create the right function which follow the sentence mentioned?


Hope you all can help me