Can I use the formula area in excel to get a remainder... 1

[AccessGuruCarl]

Hello All,

I have a spreadsheet that gathers data from several other worksheets. The last worksheet is suppose to display, Total Cases Needed, and the remaining partials if any...

worksheet layout:

Row of Qty per case. 12 columns(Items)

Next Row: Line Item Totals(per item) from 4 different worksheets.

Then I have several rows that follow:

Cell C12=Item1: D13=CaseCnt D14=ContCnt
.... Item2
.... Item3

I formatted cell D13 as a number with 0 decimal places.
I then use this formula in cell D13: =(F9) / (F6)

Cell F6 being the Qty per case and F9 is total items needed.
This works great... Example 6 per case, Qty needed is 14...

How do I get cell D14 to return the balance of 2 in this example?

I tried this: =(F9) - ((D13) * (F6))
=total items needed - (CaseCnt*QtyPerCase)
but it returns 0.0 unless you carry it out 7 decimal places

Any idea's anyone....

I was also wondering if the immediate IIF statement could be used... Some case qty's are only 1, where then all I need is the total items needed. But then how do write the formula again for cell D13 to write a zero testing for a remainder???

I'm trying to stay away from VBE editor, as I don't want the macro warning to load at startup. But if it's the only way... A small piece of sample code would be appreciated...
I have no idea where I'd begin writing the code in excel...
Although I am familiar with it's VBA editor it's the excel vba I have no real knowledge of...
Thanks in advance...


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[GlennUK]

Remainder from division is obtained by using the MOD function.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Gavona]

Formatting to show 0 decimal places does not change the fact that the cell will hold the precise number (unless you set "Precision as Displayed" in Tools, Options, Calculation).

You can do as you want though using the Int() function.

D13: =Int(F9 / F6)

You may also want to look at absolute and relative cell addresses if you want to copy these formulae to other rows.


Gavin

 

[yogia]

Hi Carl:

For full number of cases ... use =F9/F6
For number of items in addition to full cases use = mod(F9,F6)

so

[TT]
	                                                        formula	   result
number of full cases neede in cell D13 .................	=INT(F9/F6)	2
number of additional items beyond full cases in cell D14	=MOD(F9,F6)	2
		
qty per case in cell F6	.............................     6	
total qty needed in cell F9 ............................	14
[/tt]

I hope this helps.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AccessGuruCarl]

Thanks everyone for the posts...

yogia, here's a star... works like a charm....



AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

Ran into one problem...

As i mentioned there are 12 columns(items) on the the CaseTotals worksheet

The main worksheet as the 12 columns...
At times not all 12 columns are being used.
So they set the Title cell to: Not in use
and the Qty per Case to 0. All the other worksheets grab there values from the main worksheet.

Now on the CaseTotals worksheet I get error message #Div/0! in those cells where case totals and container totals are.

Can I set these to zero using a IIF statement in the formula?

Thanks...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[yogia]

Hi Carl:

I think we are on the right track ... you are now doing some real world situation error trapping. To take care of the situation where Case Qty is 0 ... in the following I have modified the formula for the case where case qty is > 0 because we would not want a situation where case qty is < 0 either.

For full number of cases ... use = if(F6>0,F9/F6,F9)
For number of items in addition to full cases use = if(F6>0,mod(F9,F6),F9)

I hope this helps.







Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AccessGuruCarl]

Thanks Yogia,

I found a similiar solution, I was about to paste.

Here is what I found in help...With a little modification.

For case:
=IF(N6<>0,INT(N8/N6),"0")
For containers:
=IF(N6<>0,MOD(N8,N6),"0")

Thanks again...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[yogia]

Hi Carl:

Great Job in finding a solution that works for you.

There are some subtle differences in the solution you posted and the one that I posted. Please look at which one serves your need best and adopt that. As you move forward, you may find that you have some other possibilities or scenarios to guard against ... but once you have the basic solution, you can always make the adjustments as the need arises.

Good Luck with your project ... and let us keep EXCELing.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com