Noob needs help for math/MS Excel formula

[mrdane]

Hopefully somebody here can clue me into figuring out a math problem.

I'd like to create an Excel spreadsheet that will allow me to enter my numbers and have it calculate the results, but I'm not sure how to do the calculation (or for that matter, perhaps how to enter it into Excel...) So here goes:

This is an example: I have a total sum of 98.61 made up from two numbers 62.89 and 35.71 What I'd like to do is vary the amounts but preserve the relationship (ratios, percentages?) between the numbers. The purpose is to round out my add-in numbers to nice round figures (like 50 or 25 or 30 or 125 or whatever as long as the they come out as nice round figures.) The resulting sum of 98.61 can vary up or down as much as needed. The ratio between the numbers can vary a bit (I can accept some degree of variation) but ideally not too much.

The Excel formula would then allow me to perform the same calculation on a set of three other numbers (and possibly a sum of 3 or 4 add-ins.)

For someone doing calculations all day, this is probably a remedial calculation but I would appreciate the help.

Anyone want to point me in the right direction?

TIA!

 

[NeutronStar]

If I understood your post the following might work for two numbers.

The ratio between your numbers is (62.89)/( 35.71) = 0.567816823024328

In a blank spreadsheet type that number into cell B1.

Or you can put it in as an equation as "=62.89/35.71" (without the quotes)

So cell B1 holds your constant ratio.

In cell A2 type in the following formula "=A1*B1" (without the quotes)

In cell A3 type in the following formula "=SUM(A1:A2)" (without the quotes)

Now type any number into cell A1.

Cell A2 will automatically show the second number you need to add to it.
Cell A3 will automatically show the total of the two numbers.

If you want to do this with more than two numbers you may need to have ratios set between every two of the numbers that you are adding. That will be a little more tricky but that's your problem.

Hope this helped.

 

[mrdane]

Thanks for the reply but I think I didn't ask my question very well. I also posted this same problem on an Excel Help Forum I found online with this wording:

I have a set of integers that I need to maintain the ratio between them while rounding to the nearest 5. For example;

62.89 plus 35.71 = 98.61

I'd like to convert 62.89 to a round number to the nearest 5 (like 25 or 50 or 125 or whatever) and convert 35.71 the same way (60 or 150 or 70 or whatever) while preserving (within some degree of variance) the ratio of the two numbers to each other and the total sum. The total sum can vary up or down as much as needed to arrive at round numbers with a similar relationship to each other and the total sum.

I've like to be able to enter new numbers and have the spreadsheet calculate the near efficient round numbers (I would even like to be able to calculate the same thing for sums of 3 or 4 addins.)

Can anyone point me in the right direction?

Thanks again for replying...

 

[mrdane]

Anyone? Anyone?

I guess what I'm really looking for is how to figure out a common multiplier to get both of my figures up to a round number. BTW, these are dollars amounts (i.e. $62.89 and $35.71) and I'm trying to get them to round amounts for sake of simplicity.

 

[Aphex_Twin]

You mean to "round" to the nearest number multiple of 5. It's easy:

=int(your_cell/5)*5 [+or not 5]

You check the value of int(your_cell/5)*5 and that of int(your_cell/5)*5+5 and with an if function calculate which diverges the least and chose that.

 

[NeutronStar]

I'm pretty sure that I could do what you want using Excel. I'm not sure if I want to go through that much work for a problem that isn't mine though.

Then I'd have to type in an explanation of what I did to boot!

,…while preserving (within some degree of variance) the ratio of the two numbers to each other and the total sum.

If you want to preserve your ratios to some degree then it can get a bit complicated. In other words, how you round the second number may depend on how you rounded the first number. If the first number was rounded up by a lot, and the second number was rounded down by a lot that's going to mess up your ratio quite a bit. So your rounding algorithm for the second number should take into account what was done to the first number to round it. Do you see what I mean?

That can actually be done by using the sign of the difference between the original first number and the newly rounded first number. If the difference is positive, you rounded up, if the difference is negative you rounded down. (This assums that the difference is found by subtracting the original first number from the newly rounded first number)

I think you'll have to use IF statements (or formulas) to accomplish this. There may be other ways.

Are you familiar with using the Excel IF formula?

If so, are you familiar with using nested IF formulae?

If so, you're home free!

If not, this is going to get real ugly.

There may be other ways to do this. Perhaps someone else has a better idea?

Personally I'd do it using Visual Basic instead of a spreadsheet. But that's me.

(I would even like to be able to calculate the same thing for sums of 3 or 4 addins.)

Actually, once you've done it for two numbers extending it to more shouldn't bee too difficult. That would just be a matter of copying and pasting the IF formulas and modifying each one slightly. In fact, if you’re real clever you could probably do it by just filling down.

Unfortunately I don't have time to invest in creating the first IF statement for you.

Let me know if you are familiar with how to use the IF statements. If not (no pun intended), then I might consider giving you a simple example to get you started.

Can anyone point me in the right direction?

I'm pointing you toward using IF statements. Like I say, there may be more than one way to skin this cat, so someone else may be able to point you in another direction as well.

You'll also need to be able to do some math using other Excel functions too of course. If I had the time I'd write up an example spreadsheet for you, unfortunately I have my own work to do.

It's going to get a bit involved I think. How important is that you accomplish this feat?

 

[mrdane]

I'm afraid this is going to be UGLY then... 'cause I'm not familiar with the IF function in Excel. I appreciate your help, and I think I can find out how to use IF on my own by using Excel Help or searching on the Excel Help Forum.

I found this to round to the nearest 5 (=ROUND(D4/5,0)*5) but I don't think that will really be applicable in what I'm trying to accomplish.

Actually, this is important for me to use so I'm more than happy to devote the time to figure it out as long as I know I'm looking in the right direction. BTW, thanks again. I might need clarification on a few points as I look into your suggestions.

Dane

 

[NeutronStar]

I'm afraid this is going to be UGLY then... 'cause I'm not familiar with the IF function in Excel.

In that case, I'd strongly recommend just playing around with the examples given in the Excel help first. Especially with nested IF statements.

They aren't hard to learn. Once you're comfortable with how they work then try doing just the simple two number problem. Keep in mind how you might keep the if statements a bit abstract so that you can use them later to fill down on a column of numbers.

Here's a tip,...

Do as many intermediate results in separate cells (like the actual rounding etc.). Then just reference those cells from inside your IF statements. Keep your IF statements as clean as possible. That way it will be easier to abstract them later for filling down a column.

You might actually end up with several columns of intermediate calculations that you can refer to from inside the IF statements. It keeps the IF statements much easier to manage, troubleshoot, and modify.

Hope this helps.

 

[mrdane]

OK, I've got some more time here to take a crack at this and, at this point, I'm still not much closer to a solution.

I guess one way to look at it is, I need a a single multiple applicable to both numbers the result of which can be evenly divided by 5.

 

[NeutronStar]

,...I guess what I'm really looking for is how to figure out a common multiplier to get both of my figures up to a round number.,...

BTW, these are dollars amounts (i.e. $62.89 and $35.71) and I'm trying to get them to round amounts for sake of simplicity...

I guess one way to look at it is, I need a single multiple applicable to both numbers the result of which can be evenly divided by 5.

I'm not sure exactly what you are trying to do here.

Can you do this by hand with the two-number example that you gave? (i.e. $62.89 and $35.71) Or any other simple concrete example?

If you give me a concrete example of what you are doing with these numbers I might be able to help you get Excel to do it. But if you don't even know how to do it by hand how are you going to write a formula for Excel to do it?

Excel can't read your mind.

I'm not even sure if what you are trying to do is even mathematically possible?

You want to find a single number that can multiply both $62.89 and $35.71 and return two new whole numbers that are both exact multiples of 5?

That sounds like a number theory problem to me. I'm not even sure it can be done. How that would help in a money problem escapes me too. Earlier you said that you only wanted to do with within some error (i.e. not exact)

I think you need to solve the problem mathematically first before you worry about how to do it in Excel. Posting just one concrete example would go a long way! Can you do this with any two dollar amounts? Do you know that it can be done with all possible dollar amounts?

The first thing you need to do is write out precisely what you're trying to do. Then get some egghead on these forums to help you do it mathematically. (maybe even post it in the Number Theory forum) And then finally, worry about how that result can be accomplished with Excel.

I'm pretty good with excel. Figuring out number theory problems is not my strongest area. Especially when I'm not really clear on what you are trying to accomplish.

If you figure out the math, I might be able to help you get Excel to do it.

 

[mrdane]

Well, I've put together a very elementary (crude even) spreadsheet that sort of does what I'd like to do. Yeah, the math has my head spinning. I can send you the spreadsheet I cobbled together if you're willing to take a look-see of what I'm looking for.

 

[NeutronStar]

I have no clue why you are trying to do this, or what's so important about being a multiple of 5. Is this for some kind of vending machine business where the machines won't take less than nickels or something?

If it were me, I'd just jack the price of everything up to the closest nickel (add 10 more cents for my time) and then load up the machines with the products.

 

[mrdane]

The multiple of 5 is important because I'm looking for round-looking dollar amounts; like $25, $75, $255, $300, $350 and whatnot. The math is important because on this financial transaction the profit margin is so very slim (the profit percentage works out to be 100 minus the sum of my two -or three or four - figures.)

What I've done, to make do for now, is run a column of figures in intervals of 5 (e.i. 5, 10, 15, 20, 25, etc...) I then divide each number in the column by my first number ($35.71) to arrive at my first multiplier. I then use each corresponding multiplier to multiply my second number ($62.89) to convert my second number into a new number - adjusted up or down by the same ratio as the first number. Then I just look for numbers that are nearly close to a round figure, for example $230 and $405.06 (rounded down to $405.) I want to do this so I can increase, at my discretion, the amount I'm investing without totally eroding my profit margin.

I've opened up emailing on my profile, in case anyone would like to email me on this problem.

 

[Aphex_Twin]

Well, you could represent $62.56 as $63 and $-0.44 (your error), as 2 separate cells. You constantly add up the current error with the previous one and when it gets to $5 you take it into consideration.

 

[mrdane]

Here's a sample of my current setup. I then find a number on the far right that's fairly close to a significant number and round it off (as illustrated.) It doesn't provide for three or four entries (I also need to be able to change the dollar amounts of me entry numbers) and it doesn't limit the output to just near significant numbers. I think NeutronStar has it right that a egghead is needed to develop an elegant solution.



$35.71 and $62.39 (base entries)

$5 /35.71 =0.140016802 *62.39 =$8.736
$10 /35.71 =0.280033604 *62.39 =$17.471
$15 /35.71 =0.420050406 *62.39 =$26.207
$20 /35.71 =0.560067208 *62.39 =$34.943 roundup to $35
$25 /35.71 =0.70008401 *62.39 =$43.678
$30 /35.71 =0.840100812 *62.39 =$52.414
$35 /35.71 =0.980117614 *62.39 =$61.150
$40 /35.71 =1.120134416 *62.39 =$69.885 roundup to $70
$45 /35.71 =1.260151218 *62.39 =$78.621
$50 /35.71 =1.40016802 *62.39 =$87.356
$55 /35.71 =1.540184822 *62.39 =$96.092
$60 /35.71 =1.680201624 *62.39 =$104.828 roundup to $105
$65 /35.71 =1.820218426 *62.39 =$113.563
$70 /35.71 =1.960235228 *62.39 =$122.299
$75 /35.71 =2.10025203 *62.39 =$131.035
$80 /35.71 =2.240268832 *62.39 =$139.770 roundup to $140


Aphex_Twin, your idea has me thinking. I'll have to see if it would achieve similar results.

 

[NeutronStar]

mrdane,

I've constructed a spreadsheet that does exactly what you did in the previous post. It allows you to type your dollar amounts into named cells so that you can change the dollar amounts very easily.

You're routine here only considers rounding up. I've included rounding down also. I've also included two columns that display the actual errors.

I can't send it to you via PF's email system because they don't allow attachments. But if you send me your regular email address via PF's email I'll send you a copy of the spreadsheet using my regular email.

You'll have to try to modify it yourself for doing more than just two numbers.

 

[mrdane]

mrdane,

I've constructed a spreadsheet that does exactly what you did in the previous post. It allows you to type your dollar amounts into named cells so that you can change the dollar amounts very easily.

You're routine here only considers rounding up. I've included rounding down also. I've also included two columns that display the actual errors.

I can't send it to you via PF's email system because they don't allow attachments. But if you send me your regular email address via PF's email I'll send you a copy of the spreadsheet using my regular email.

You'll have to try to modify it yourself for doing more than just two numbers.

That sounds great! Thank you. I sent you my email address, you'll have to tell me if you don't receive it. Thanks again!

 

[NeutronStar]

Keep in mind that this is just a crude construction. You may need to tweak it for your purposes, and it also hasn't been tested for possible erroneous output.

It's just something to get you started.

Hope it helps.