Calculating Percentage

[robsworld78]

Hi, this is a little hard to explain for me. I'm trying to find the percentage of a number.



I have a form with 2 subforms, the first subform is called "PreOrderToOrderOrdersSubform" and it has a field on it called "WeightStart" in this field we enter in the weight of the order when its put on the scale after being picked. Its just a text box set as fixed number 3 decimals.

One the 2nd subform which is called "PreOrderToOrderInventorySubform" all the product for the order is entered. It totals up the weight of each item and in the footer I have it summing all the weight of each item its called "SumOfWeight"

Now on the main for I have an unbound textbox which takes the "WeightStart" and subtracts it from the "SumOfWeight" now this tells me how much its out, this will catch any picking errors. This is the code I use for that.

=([PreOrderToOrderOrdersSubform].[Form]![WeightStart]-[PreOrderToOrderInventorySubform].[Form]![SumOfWeight])

Everything works great but I want to convert that number into percentage but a true percentage. This is were I can't explain, I hope you follow me.

Example:
If "WeightStart" is 0 and "SumOfWeight" is 9.93 then the percent out should be -100%

If "WeightStart" is 19.86 and "SumOfWeight" is 9.93 then the percent out should be 100%

All I can get the total to show is -99.3% or 99.3% because 0 - 9.93 = -99.3 and its just adding a percent sign when I want a true percentage.

I hope you know what I'm talking about.

[apr pillai]

When 9.93 is -100% when weight is 0 then -1% is -9.93/100=-0.0993. If you plot this value on a scale it will look like below:

1%         2%           3%           4%           5%      .....    100%
0.0993      0.1986      0.2979      0.3972       0.4965            9.93

Let us say the result of your SumOfWeight is 0.4975 then the percentage is calculated on a 0 weight scale as  =-int(SumOfWeight/9.93*100) = -5%.

We have added the negative symbol at the begining to make the result as negative because the weight starts from 0,1,2,3,4,5 to 100. The INT() function is applied to round-off the percentage into an integer. If you prefer the value like -5.01007 then don't use INT() Function.

Since the SumOfWeight is always 9.93 or less the calculation can be made the same as above for the weight starting value 19.86 scale also, but make the percentage a positive value.

To find out the Weighted Value of SumOfWeight = SumOfWeight/9.93*100*19.86

The above expression can be shortened, but I left it like that for clarity.

[June7]

Not clear to me. Define 'true percentage'. Show your forumula. Do you just want to round to whole number?

You subtract WeightStart from SumOfWeight?
9.93 - 0 = 9.93 = -100
9.93 - 19.83 = -9.93 = 100% - Really? Why? Hmm, 50% used but 100% met

Why not WeightStart - SumOfWeight?

What if WeightStart is 23.07?
9.93 - 23.07 = -13.14 = 100%?

[robsworld78]

Yeah this one is tuff for me to explain, I hate it cause I can't search for anything remotely close to what I want.

When I say "true percentage" this is what I mean.

say you were going to buy something at the store that cost $15.95 but you got it for a deal at $8.95 what percentage did you save? You cant just subtract the 8.95 from the $15.95 and slap a % sign on it, which is what I'm doing.

This is how it works. Product is given out everyday to different vendors, the product is picked and then weighed, that weight is entered in the database as the actual weight of product. Then in the database for each product is the weight, so when you fill an order with lots of product it totals all the weight the order should be. If the actual weight is different from what the weight should be based on the order we know something is either missing or there is extra. We allow these 2 numbers to be out by 3%, if its over 3% we recount everything and go from there.

So right now I have a field that I enter the actual scaled weight as I mention, and I have a field that's calculated on the form which sums up the weights on the order. Then I have a 3rd field which subtracts both fields to tell me the weight difference.

So if my weight was 11.392 on the scale and in the computer it was 10.392 my 3rd field says I'm out by 1kg which is ok, but now I want to know how much that 1kg is in percentage. Is the order out 10% or is it out 6% or in the limit and maybe out 2.33%.

Because order weights are all different if you only see how many kg's its out you don't know what percentage of the order that is.

Does that makes sense?

So all I've done is a simple subtraction between the 2 weights and it gives the correct outage in kg then I tried changing the field type to percent and it just adds the percent sign to the kg out but that isn't my so called "true percentage", I need it to say ok that 1kg difference is 6% of the order or whatever it is.

Ignore my first post it didn't come out the way I wanted, the negative numbers will happen just as much as positive because the order could be out either way, so the percentage box could be out 3% or -3% depending if it has more or less.

[apr pillai]

It is more clear now.

So the target Scaled weight is Order-Weight. If the SumofWeight (or Total scaled-weight) is less than the order-Weight then we should get a negative percentage indicating that the scaled weight is less than the Ordered weight. If the derived percentage is positive then the scaled weight is more than the Ordered weight.

Use the Expression: (ScaledWeight - OrderedWeight)/ScaledWeight

Example1: When the Scaled weight is less than the Ordered weight.

Ordered Weight = 20.575
Scaled Weight = 19.75
Scaled Weight Percentage = (19.75-20.575)/19.75 = -0.04 (or -4% less weighed than ordered)

(when you format the text box to percentage then gets -4% or multiply it with 100 like (19.75-20.575)/19.75*100 to get -4)

Example2: When Scaled weight is more than the Ordered weight.

Ordered Weight = 20.575
Scaled Weight = 20.752

Scaled Weight Percentage = (20.752 - 20.575)/20.752 = 0.01 (or 1% in excess.)

[robsworld78]

thanks, that seems to work fine.

When I have nothing in scaled weight the calculated field says #Div/0! is there anyway to get rid of that, I'm guessing it means it can't divide by 0.

If I have a weight in scaled weight and nothing in product weight it shows 100% over which is cool. Can it do that if nothing is put in scaled weight, as in -100% instead of the #Div/0!

Thanks for the help

[June7]

If there really were nothing in ScaledWeight, would be Null, calculations with Null return Null. The question is why is there a 0 in the field?

Whether the field holds 0 or Null for no data, must give the expression an alternate value.

If field holds 0:
(ScaledWeight - OrderedWeight)/IIf(ScaledWeight=0,1,ScaledWeight)

I don't allow 0 unless it is valid data, meaning I never use 0 as default for number fields. If I have no data I want Null. So if field had Null could use:
(ScaledWeight - OrderedWeight)/Nz(ScaledWeight,1)

[robsworld78]

Sorry I was the reason for the #Div/0!, I added Nz funtion to each field, after removing that the field comes up as blank. It will stay blank until something is entered in the scaled weight. I tried you IIf but it appears to work the same with or without that. The last scaledweight in your first expression is that the field name again? Or control name?

[June7]

Field name or control name, shouldn't really matter. When I first converted to Access 2007, seemed to see performance issue if expressions referred to field name. So I give controls a meaningful name different from field and usually refer to control. Examples of control names:
tbxWeight
cbxCustomerName
lbxLocation

So your field will now have either a value greater than 0 or Null, therefore, first expression will never encounter 0 and the IIF is not needed. Go with the second expression if you want something to show other than Null.

[robsworld78]

That's good to know, I'll try and start using control names instead.

Well I've got things going for weight as I want, I didn't need you formulas for that because I changed things a bit, but I do need something like that for my volumes, I've tried both and they don't work.

My volume tracking works the same way as weight, but change the word to volume.

Here's my expression right now.

=([OrdersOrdersSubform].[Form]![CoolerVolume]-[OrdersInventorySubform].[Form]![SumOfVolume])-[VendorInventoryLevelSubform].[Form]![SumOfVolume]

That works good when data is available in all the fields but here's the problem.

If a vendor doesn't have any inventory of there own it will result #Type!, if there is inventory it results null until a new order is filled and then does what I want.

If I add you Nz function it will result #Size! if there is no current inventory, once there is inventory it will result null until a new order is filled same as above.

How can I have it do only this part of the expression if there is no data in the VendorInventoryLevelSubform

=([OrdersOrdersSubform].[Form]![CoolerVolume]-[OrdersInventorySubform].[Form]![SumOfVolume])

Thanks!

[June7]

Sorry, I'm lost on this. Never had those calc errors show up. Must have something to do with the aggregate calc SumOfVolume which has nothing to calc if there are no records. Issue with IIf is that all parts of the expression must be able to be evaluated, even the part that doesn't return the value, if any part errors the whole expression errors. So not sure an IIf would help in this case.

[robsworld78]

Yeah I think that's the problem, its because I'm using SumOfVolume which doesn't have anything to calculate when there are no records. When I was playing with weights I was originally going to pull this method and was running into the same problem but after a new thought on how it should work I didn't need to pull from that SumOfWeight.

There must be some way around this though, is there anything I can add to the field SumOfVolume on the form? I've tried the Nz there but nothing.

[robsworld78]

it doesn't have anything to do with the field SumOfVolume, if I go right to the bound field on the subform, LeftVolume it does the same thing unless 1 item exists.

How would I use an Iff statement that says if this doesn't exist ignore it? Starting where I put the Iff

=([OrdersOrdersSubform].[Form]![CoolerVolume]-[OrdersInventorySubform].[Form]![SumOfVolume])Iff-[VendorInventoryLevelSubform].[Form]![SumOfVolume]

[June7]

Hope someone else has idea, this is totally unfamiliar to me. If you want to provide the project I will see if that helps me figure it out.

[robsworld78]

Well thanks for getting me this far, I've been messing around with it a bit but no luck as of yet, hopefully someone can throw some more advise in.