Email:      Pass:    Pass?

Free Excel Forum

Divide 2 Numbers And Have 2 Decimal Remainder Format

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello all,

Need your help...

My objective is to format the remainder of 2 numbers that were divided to a two-number format.

For example, in cell B3 I have $7.47 and I am dividing by 31 (days in a month) (in cell D3) and then gettting the remainder of 0.240967741935484. I would like to be able to just format the remainder to .025 (basically rounding it up the next highest number.

Can you please help?

FOR YOUR INFORMATION - D3 has the formula of =IMDIV(B3,31). Don't know if that's right or not.

Thanks in advance all.

Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Format Cells as an Accounting Number in Excel Number Formatting
- This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is

Similar Topics

I was given a challenge by a friend to find a certain number.
This number when divided by 2 gives a full number and a remainder of 1
The same number when divided by 3 gives a full number and a remainder of 2
Again when divide by 4, remainder of 3
devided by 5, remainder 4
and so on until when divided by 10 gives a remainder of 9
Is there really that number?

Please see my attachment. I'm simply trying to manage the results of a division problem. We all remember the days where you learned to divide and the show the solution as integers, decimals, and remainders. For the cells e7, h7, and so on, I want to calculate the quotient, but I want to show the results in any of those formats. So, if the division type (A10) is set to Remainder, it should show remainder format. How can I calculate the quotients for all of these cells (and more!) when you click "Show Answer Key" AND show the correct format (decimal, whole, or remainder)? I'm new at VBA, and this has me stumped. I just don't know how to tell it to calculate all of the equations at once and change the format accordingly.

I am organizing a conference for which a sponsoring company typically sends their staff people to attend. I am trying to incentivize companies to send the max # of staff by offering 2 things:
1. Discount on registration fees (% proportional of the number of delegates sponsored)
2. Complimentary registrations

For example:
55 delegates: 5 free registrations + 30% discount on remainder
56- 60: 7 free registrations + 35% discount on remainder
61-70: 10 free registrations + 40% discount on the remainder
71-80: 15 free registrations + 45% discount on the remainder
>80 delegates: 20 free registrations + 50% discount on the remainder

In the above example, I actually make less money as the number of sponsored delegates goes up! Clearly, I need to reverse that. The objective is to have revenues go up with more registrations, but with my margin of profit tapering with more registration.

Any ideas how to do that in excel?

Thanks in advance

I've done this before in excel, but I don't remember what it is called, but it works something like this.

Let's say you are trying to figure out the lowest integer (other than 1) that will divide into another integer without a remainder.

For example, the lowest integer that will divide into a 9 without a remainder is a 3.

So, let's say I'm trying to do this in excel. I have three cells.

cell_A = the number I am dividing into. In the above example this number would be a 9.

cell_B = the number I am trying to figure out. In the above example, this number is 3.

cell_C = cell_A / cell_B, such that the result of this formula does not give a remainder... mod(cell_A / cell_B) = 0.

How do I formulate cell_B so that cell_C meets the above condition.

Hello. im wondering if anyone can help me with this excel problem... i want a cell to give me the sum of a range...i want it to stop adding at 40 and the remainder to go to a different cell.. i also want to know if two cells can do this and one cell catch the remainder of both

Hi… can someone help me on a problem in using MS Excel 97. Would really appreciate your input!
My data is generated from a macro (file A) and then copied (copy values) to a template file (file B) which has some formulas linked to another file (file C).

The file B is pre formatted with number/fonts/alignment etc.

My problem is that for one column in this file, the data are eg:

Current format

Required format

I need to format (once) all cells that have values as in “required format”. I have tried using the comparison operator in format cell/custom : [>1] #,###;#,###.0 but it doesn’t work because it needs to obtain the remainder of the value before the comparison operator can be used, I think. I tried using MOD(cell,1) which only returned the value of the remainder. But I got lost there. Could someone please help so I could get the data in the required format? Thanking you in advance!

Hello all,

I cannot, for the life of me, figure out what the hek the MOD function is supposed to do. According to the MS Help file, it returns the remainder of a number divided by another number. Now, when I was in school, the remainder was pretty much anything after the decimal. That doesn't seem to be the case in Excel, however.

For the examples here, let A1 equal 5.

The formula =MOD(A1,2), as I see it, should return 5 since 5/2 equals .50, but it doesn't; it returns 1.
Further, 5/3 equals .67, but =MOD(A1,3) returns 2 (what, is it rounding?)

There's a lot of talk about MOD being used to return even/odd results, but does this work when the MOD function uses 1 or 3 (or higher) as the divisor?


Starkman (very frustrated)

I need to return a boolean T/F value for a string that is 7 characters. I need to check if the last alpha character is validated according to the following structure. I can do this entirely in excel using multiple cells, but I wish to have it in a sub function so i can call it in my conditional field
example =MyFunction(A1)

The letter shall be validated as follows:
i. Add the six individual numbers which result from the products of each of the first six digits times the digit's position in the number stream, with position one being the left-most digit (see Step 1 below).
ii. Subtract multiples of 22 from this total until the remainder is less than 22 (see Step 2 below).
iii. The remainder is then compared against the following table to find or verify the alpha code:

Remainder Alpha Code Remainder Alpha Code Remainder Alpha Code
0-A 6-G 12-N 18-V
1-B 7-H 13-P 19-W
2-C 8-J 14-R 20-X
3-D 9-K 15-S 21-Y
4-E 10-L 16-T
5-F 11-M 17-U

Example: 836597H
Step 1. Compute Numeric Code.
= [(8x1) + (3x2) + (6x3) + (5x4) + (9x5) + (7x6)]
= (8 + 6 + 18 + 20 + 45 + 42)
= 139
Step 2. Determine Remainder for Alpha Code.
= 139 - (subtract multiples of 22 until you get a number that is less than 22)
= 139 - (22 x 6)
= 139 - 132
= 7
Step 3. Verify the Alpha Code.
The alpha character represented by the number 7 is the letter H (see table).

Please help.

I would like to set a format for a cell to show the remainder of a quotient. For example, if 26 divided by 5 equals 5 R1, I want that to appear in the cell, not a decimal. Is this possible?

I thank you kindly for your prompt response.

i am looking to do a simple formula but i am unable to get this work correctly... what i would like is the following:

A1 = 2

D1 = 20
E1 = 60
F1 = 60

the above numbers are manual input and no formula is needed for those.. below would be where i am having some trouble..

I1 = D1
J1 = E1*A1 but if J1 is over 100 then add 1 to I1 and keep the remainder in J1
K1 = F1*A1 but if k1 is over 100 then add 1 to J1 and keep the remainder in K1

So in short K1 and J1 above have a ceiling of 100, but if the number is anything over, 150 lets say, add 1 to j1 but keep the 50 in K1 and the same for J1 adding to I1..

if anyone could help with this, that would be appreciated..

I want to print two different formats on one page without having to reload the page in the printer. I have two page formats on one sheet. The print areas that have different row heights and column widths. Currently I print one print area and then load the page into the printer so I can change the print area to the other format to print the remainder of a page.

Is it possible using VBA to print one part of a page, prevent a page break so I can change the format of the print area and then continue to print the remainder of the page from a different print area which contains a different row/column format than what I printed on the top of the page?

If this is possible can you outline the steps needed to accomplish this task or point me to a discussion on how to do this? One thing I don't know how to do is prevent the page break. When I print one area it finishes part of the page and ejects the page. I need to stop the printer from ejecting the page so I can change the format for the remainder of the page.


I have a userform for the staff to capture cargo shipment details. Each shipment has a unique identifier called an Air Waybill (AWB). The AWB is made up of three parts

the Airline prefix (3 digits)
the serial number (7 digits)
the check digit (1 digit) - The check digit is derived by dividing the 7 digit Serial Number by 7. The remainder determines the Check Digit. Example: Serial Number 8114074 divided by 7 is 1159153 remainder 3. Therefore the Serial Number + Check Digit is 81140743

We are currently entering an AWB in the following format 236-81140743

I am having lots of issues of staff entering incorrect AWB details whether that be missing digits, incorrect format or incorrect check digit.

Apart from bashing them over the head each time they enter the details incorrectly can anyone offer me any solutions to highlighting the error at time of entry????

I want to be able to take a number and determine if it is greater than or equal to 100,000. If it is greater than 100,000, I want to take the first 100,000 and multiply it by 0.10%, and the remainder by 0.08%. If the number is over 200,000, then I want to multiply the first 100,000 by 0.10%, the second 100,000 by 0.08% and the remainder over 200,000 by 0.04%. I would like to put this in a working forumla so that I can change the percentages and numbers. Thank you!

Gents can you please help..

i'm trying to create to a sheet that has values divided on 18 months i would like to be able to change any of the values and have the remainder divided on the rest of cells:

A1+B1+C1 = ToTAL


so if i change any of the values i should be able to see the remainder divided on the rest of the cells. The Total don't change.

Thank you in advance..

I have a calculation that I do not even know where to begin with. It is a
check digit calculation. I can easily come up with the sum, but then the fun
begins. I need to divide the sum by 10. Seems easy enough, except that I
need to end up with a remainder, not a fraction or decimal. So if i end up
with 79, I need to end up with the 9 or eventually after subtracting the
remainder from 10, the 1. In this case the 1 is the check digit. Any ideas?

Your question leaves a little to be desired
We arent mind readers
Rounds a number down to the nearest integer.
Number is the real number you want to round down to an integer
Returns the remainder after number is divided by divisor. The result has the
same sign as divisor.
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.
If divisor is 0, MOD returns the #DIV/0! error value.
The MOD function can be expressed in terms of the INT function:
MOD(n, d) = n - d*INT(n/d)
remove nospam for email addy!

Hi there,
Setting up a worksheet to check a string of 7 numbers has the correct check letter attached. Example
X4032818 X is the check letter. One method is take the 1st number multiply by 1, 2nd multiply by 2, 3rd multiply by 3, 4th x 4, 5th x 5, 6th x 6, 7th x 7. Add the results, in this case equals 123. Divide this result by 25 (the letter Z is not used) the remainder (23) matches the 23rd letter in the alphabet. To cater for when the remainder is 0, the range is 0=A Y=24.
I am using MID to extract each number


Instead of six seperate cells in Row C using MID is there a quicker way?

I am working with the MOD function and percentages and sometimes it works right, sometimes it doesn't. Not sure if I am having a brain lapse or not.

For Example,
If I do MOD(1,50%), I get zero because there is no remainder. (1/50% is an even 2.00).
If I do MOD(1,25%), I get zero because there is no remainder. (1/25% is an even 4.00)

However, if I do MOD(1,5%), I get 5%, but I should get zero 1/5% is an even 20.00

Am I doing something incorrectly? Any feedback helps. Thanks.

I used a comand a few weeks back which took a simple calculation and returned the remainder, e.g. could do a sum like 38/6=2
but i cant remember what the command was or how it worked.
can anyone jog my memory

I need a formula that converts ounces into gallons that show the remainder not as a decimal but the remainder as ounces.

the formula that I am using =convert(A1,"oz","gal") works great but leaves me with a decimal. 7.6 I need it to read 7 gal 77 ounce

Can someone please advise the relevant Excel functions to use to determine if one number divides into another without any remainder?

i want to use excel in the following way:-

if i place a binary number in any cell, it converts it in Binary Coded Decimal Number and present the result in MSB and LSB format in two cells.To

convert a binary number into an unpacked BCD, divide the binary number by decimal 10 and place the quotient in the most significant byte and the remainder in the least significant byte.

i want to use excel in the following way:-

if i place a binary number in any cell, it converts it in Binary Coded Decimal Number and present the result in MSB and LSB format in two cells.To

convert a binary number into an unpacked BCD, divide the binary number by decimal 10 and place the quotient in the most significant byte and the remainder in the least significant byte.

I'm not sure if this would be the correct place to post this, if not, I'll repost it where it should go.

I've been trying to tackle a programming issue for a few days now, and I've made no headway. I'm trying to setup some if/then statements for some calculations, but I really have no idea what I'm doing. I've read some other webpages about it, and still am pretty clueless. I'll explain the situation below.

I've got five variables: a, w, x, y, z. I need to write something that will do this: It needs to check and first see if z is a number greater than 0. If so, it would need to subtract a from that number until either a or z is 0. If z becomes zero, it would need to look at the value of x + y and subtract the remainder of a from value. If the value of X + y becomes zero, it would then look at w, and subtract the remainder from that value.

Any help/suggestions would be greatly appreciated.

Hi guys, I am just a beginner in excel who needs some helps.
I have an assignement to find out the date of the first Wednesday in a given year.
I realise that if I divide the serial number of a date by 7, the remainder would tell me the day of the week (0.14285714 or 1/7 is Sunday, 0.285714 is Tuesday). But how do I extract the decimal from a division in excel? I was thinking about using the IF function to compare the remainder w/ 4/7, which is the corresponding decimal of a wednesday, to solve my problem.
Another problem is that, I cannot do DATE(2006,3,2)/7 to calculate the answer. Is there anyway to let excel understand that I need to play with the serial number of the date, but not the actual date itself?

Do you have any idea, or better solution to solve this problem?