# Thread: Automate a ratio calculation? (97 SR1)

1. ## Automate a ratio calculation? (97 SR1)

Greetings all.

I put this query to the group. I have been manually sorting cells then typing in formula to calculate a ratio for the selection (selection of cells varies). Below is an example:

A B C D E F
Group Cust# Product Qty Total Incentive
ABC 12345 AP 3 1500 =E70/((\$E\$70+\$E\$71+\$E\$72+\$E\$73+\$E\$74)/500)
ABC 13456 AP 1 500
ABC 14563 AP 3 1500

So for each defined group, I would find the products that have an incentive, then across the selection - type out and pull down the formula. Each group has a different incentive amount and the incentives may be applied to different products - ie ORanges instead of APples.

I was thinking 3 input boxes - 1 to ask group, 2 for product and 3 for incentive amount.
It would then go something onlong the line:
dim grp as string
dim prod as string
dim incent as long

my syntax is off, but in column f: for each cell where a=grp and c=prod, then f= En/((En1+En2+En3+etc.)/incent)
Since there are about 30 possible group entries, I would need to either create a reference table or do the input boxes 30 odd times.
So the variables would change, wouldn't they for each time the input box was activated?

Of course the whole problem for me is how do I get that ratio formula across an unknown number of cells (anywhere from 2 entries to 45)?
I am looking for some direction. If this can be automated in some fashion - where do I begin?

Tanx.

2. ## Re: Automate a ratio calculation? (97 SR1)

I don't know if I understand your question completely. Does the attached spreadsheet do what you want?

3. ## Re: Automate a ratio calculation? (97 SR1)

Yes and no. The ratio calculation is based on the Group more so than the product, but the product has be factored in. But I see what you are getting at - use a table reference with Sumif. I could probably incorporate the whole criteria (ie Group = ABC, Product = AP, Bonus=500) in the sumif range. Because of the number of entries, I would have to bring over a trap, like iserror for some entries not meeting the criteria. Thanks for the point in the (hopefully) right direction. I will let you know how it works on the real data.

4. ## Re: Automate a ratio calculation? (97 SR1)

Johncon, like Hans, I wasn't sure what exactly you are trying to accomplish. It would be good if you could post a sample workbook and explain a little more. If you are trying to do calcs on a combination of factors such as product, group and incentive multiplier, you may alternatively need sumproduct or an array formula, but I can't figure out your exact objective. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

5. ## Re: Automate a ratio calculation? (97 SR1)

My apologies.

It is difficult to put into words what I am trying to do. I started with Hans idea of the SumIf and I took it further with trying DSUM. The problem I have is that I do have incentives to apply to groups and their products with some groups having incentives across more than one product. Taking Hans insight and bastardizing the concept to the nth degree... I still encounter the problem where the incentive can not be found and calculated if there is more than one product line for a group. The sample file, borrowed from Hans, has been attached showing the sample with my spastic formula. The final incentives should match those in J, but for those groups with multiple product lines, alas no.
So yes, maybe arrays might be the way to go (an area of minimal experience with me), but I don't think sumproduct would do the trick.
Thoughts?

6. ## Re: Automate a ratio calculation? (97 SR1)

I'm the one who will apologize, because I'm still not getting it. One (of several) point(s) of my confusion is that both your sales calculation and incentive calcuation are coming from the same table and since I can't follow what the formula is column F is doing, I can't help. Despite which I had a shot at a variation that might be useful, see attachment for your use or abuse. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

The things I would normally ask are:

who gets the incentive you want to calculate (what is the common "collector"); product type or salesperson or customer?
How is the incentive calculated for one earner?
Is there a step up according to sales volume, product type or other basis?

It would be good if you would lay out one specifc calculation example in terms of the hypothetical workbook you attached.

7. ## Re: Automate a ratio calculation? (97 SR1)

Having made a little more progress, I see that cell F7 returns zero because the match in the second part of the AND argument hits the first appearance of Group GHI, which is PE, not AP; since then PE <> AP, the AND formula returns FALSE. The following simplified formula fixes that, if this is what you intend, for cell F2, copied down:

=E2/SUMIF(A:A,A2,E:E)*VLOOKUP(A2,H\$2:J\$6,3)

And I think I answered one of my questions; incentives are earned by Group, correct?

You may still find my earlier attempt useful as a way of organizing your data.

8. ## Re: Automate a ratio calculation? (97 SR1)

The wonders of trying to automate something. Yes, the incentives are applied by group, but I have a few instances where the incentives for a group are applied to more than one product so previous formulas only found the first incentive by group/product. After reviewing your example - I agree. I was thinking vertically, but setting up a horizontal table will allow me to use a lookup formula easily. I will have to reformat my original data table but I think this shall do nicely.
My thanks to you and Hans for the discussion and guidance!
<img src=/S/bow.gif border=0 alt=bow width=15 height=15>

9. ## Re: Automate a ratio calculation? (97 SR1)

I spoke too soon. Regardless of which tact I take, if I use the sumif function, it will sum all group codes regardless of product. Even with a mulitple criteria, it will still read true and still add all applicable groups. In the end, I went for simple ugly.

I inserted a new column as A, then added group with product to form a unique code - ie, ABCAP and ABCOR. Now when I use the sumif, using the incentives table in a vertical mode, it will calculate and pull (through vlookup) the incentive amount and only apply that calculation to that group and that product. Simple, a couple of steps more to insert the columns and add in the group and the product code - but, heh, it works. Thanks again for pointers.

10. ## Re: Automate a ratio calculation? (97 SR1)

Talking to yourself, JohnCon? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

You might be able to condense those multiple criteria with array formulae, if you'd like to post the status for now we can have a go at it. But sometimes "ugly" also means easy to understand, and easy to understand usually beats incomprehensible elegance. In my work the level of ugliness is directly proportional to the level of immediacy and inversely proportional to the likelihood of future repetition. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•