Excel experts, please help

[Vegitto]

Hey all.

I'm currently working on an assignment involving the calculation of an optimum. This optimum is based on another value and needs to be rounded accordingly.

In short: We're trying to determine the optimal ordering value. This value is optimal only if the cost of ordering (and storage) is at its lowest point. This value (cost) is based on multiple equations, including one that determines the optimum. The difficulty lies in getting Excel to round either up or down (it is only possible to order an 'integer' amount) based on the cost.

I don't know if I'm making any sense at all, but if I am, please help.

Cheers!

 

[Gibson486]

use if statements in your equation

 

[GuitarDaddy]

Not sure from your descrption what your trying to do, but look at the following math functions

Round
Roundup
Rounddown



/insert/function/round or roundup or rounddown

 

[Gibson486]

Not sure from your descrption what your trying to do, but look at the following math functions

Round
Roundup
Rounddown



/insert/function/round or roundup or rounddown

he knows that, he just wants excel to decide whether he rounds up or down based on the value calculated.

 

[Vegitto]

he knows that, he just wants excel to decide whether he rounds up or down based on the value calculated.

Exactly!

Think it worked, but I'm not really sure.. I'd appreciate any help in the mean time.

 

[Syringer]

I'm confused..if you still need help, can you post an example?

 

[Epic Fail]

The unrefined solution is to make 100 rows to calculate the optimum, after the first iteration you can narrow the optimum by a factor of 100 if you can find the inflation point. Use the input values between the inflation point, divide them by 100 and repeat, after 3 times and you have a 6 significant digit optimum.

 

[akenbennu]

INT(value+0.5) should give the correct rounding.

 

[Gibson486]

Exactly!

Think it worked, but I'm not really sure.. I'd appreciate any help in the mean time.

there are a few ways...

('if a>b', true, false) is the general equation i think.

 

[Vegitto]

there are a few ways...

('if a>b', true, false) is the general equation i think.

I used =IF(A2>A3;B3;B2)

With A2 and A3 the calculated cost (based on the rounding up and down of the supposed optimum), if the cost of A2 is higher than that of A3 (ie, A3 is lowest) B3 is taken (this value is one of the rounded optima), else B2 is taken.

You guys think this is correct?

 

[Hacp]

he knows that, he just wants excel to decide whether he rounds up or down based on the value calculated.

Ow so just use a simple if statement then.

 

[Paperdoc]

Forget the IF...THEN logical operations.

akenbennu had a simple way: INT(value+0.5) will give you an INTEGER number rounded off properly. In fact, you can use this technique to build any rounding function if you just incorporate some multiplying and dividing by powers of ten.

BUT as Guitardaddy said, just use the Excel built-in function ROUND. It works like this:
=ROUND(Arg,digits)
Argument is the number you want to round off (or the cell containing it). Digits is the number of digits AFTER the decimal point. Digits can be 0 if you want an integer only, or negative if you want to round to the nearest hundreds, thousands, etc., or a positive number if you want some digits after the decimal.

=ROUND(12.3456,0) gives 12 -OP's original question
=ROUND(12.3456,-1) gives 10
=ROUND(12.3456,1) gives 12.3
=ROUND(12.3456,2) gives 12.35

A cautionary note when using rounding: you have to understand WHEN to round off. If you round off too early in a calculation sequence, you lose some useful information. On the other hand, if you fail to round off correctly and carry forward many digits that have no real information, you delude yourself.

For example, you sell oranges in bags and they each contain a dozen oranges, approximately, but the filling machine sometimes makes errors and you already have collected data showing the mean is 12.021 oranges per bag, and you also have the standard deviation of the count of oranges in a bag. Using statistics you have calculated the maximum number of oranges ever likely to be encountered (to within a 99.7% Confidence Interval) as 15.237. So, what IS the MAXIMUM number of oranges per bag? NO bag contains 0.237 of an orange mixed in with the others. The answer is 15 - plain and simple integer. You round off here.

Now, if buyers pay by the actual count at 20 cents per orange, what is the MAXIMUM price for a bag of oranges? It is NOT 15.237 times 20 cents. It is 15 (the maximum count) times 20 cents.

Now someone asks, for purposes of loading up skids of these bags of oranges, what is the AVERAGE weight (to the nearest pound) of a skid with 200 bags on it, if we also know that the average weight of one orange is 0.397 pounds? This answer needs you to keep the significant digits in the intermediate calculation: 12.021 oranges per bag x 0.397 pounds per orange x 200 bags per skid = 954.4674 pounds. NOW is where you round that result to 954 pounds (plus the weight of the skid itself to get Gross Weight).

 

[Gibson486]

Forget the IF...THEN logical operations.

akenbennu had a simple way: INT(value+0.5) will give you an INTEGER number rounded off properly. In fact, you can use this technique to build any rounding function if you just incorporate some multiplying and dividing by powers of ten.

BUT as Guitardaddy said, just use the Excel built-in function ROUND. It works like this:
=ROUND(Arg,digits)
Argument is the number you want to round off (or the cell containing it). Digits is the number of digits AFTER the decimal point. Digits can be 0 if you want an integer only, or negative if you want to round to the nearest hundreds, thousands, etc., or a positive number if you want some digits after the decimal.

=ROUND(12.3456,0) gives 12 -OP's original question
=ROUND(12.3456,-1) gives 10
=ROUND(12.3456,1) gives 12.3
=ROUND(12.3456,2) gives 12.35

A cautionary note when using rounding: you have to understand WHEN to round off. If you round off too early in a calculation sequence, you lose some useful information. On the other hand, if you fail to round off correctly and carry forward many digits that have no real information, you delude yourself.

For example, you sell oranges in bags and they each contain a dozen oranges, approximately, but the filling machine sometimes makes errors and you already have collected data showing the mean is 12.021 oranges per bag, and you also have the standard deviation of the count of oranges in a bag. Using statistics you have calculated the maximum number of oranges ever likely to be encountered (to within a 99.7% Confidence Interval) as 15.237. So, what IS the MAXIMUM number of oranges per bag? NO bag contains 0.237 of an orange mixed in with the others. The answer is 15 - plain and simple integer. You round off here.

Now, if buyers pay by the actual count at 20 cents per orange, what is the MAXIMUM price for a bag of oranges? It is NOT 15.237 times 20 cents. It is 15 (the maximum count) times 20 cents.

Now someone asks, for purposes of loading up skids of these bags of oranges, what is the AVERAGE weight (to the nearest pound) of a skid with 200 bags on it, if we also know that the average weight of one orange is 0.397 pounds? This answer needs you to keep the significant digits in the intermediate calculation: 12.021 oranges per bag x 0.397 pounds per orange x 200 bags per skid = 954.4674 pounds. NOW is where you round that result to 954 pounds (plus the weight of the skid itself to get Gross Weight).

i think you missed his question....