Email:      Pass:    Pass?

E-mail:

# Divide 2 Numbers And Have 2 Decimal Remainder Format

Hello all,

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.

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

## Similar Excel Tutorials

Show Fewer Decimal Places Without Losing Precision in Excel
How to show a smaller number that looks better without sacrificing decimal places or precision in the actual number ...
Round Numbers Up or Down in Excel
How to round a number up or down and also to a specified number of decimal places in Excel.  This will allow you to ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...

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?

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?

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

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?

Sincerely,

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).

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.

Thanks,
Barry

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!

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

100+100+100=300
50+150+150=300

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.

Your question leaves a little to be desired
INT
Rounds a number down to the nearest integer.
Syntax
INT(number)
Number is the real number you want to round down to an integer
MOD
Returns the remainder after number is divided by divisor. The result has the
same sign as divisor.
Syntax
MOD(number,divisor)
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.
Remarks
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)
--
paul

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
Code:

```=(MID(\$C2,2,1))*2
```

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

Hello,
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
thanks

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.

Does anyone know if there is a function in vba that checks to see if a number returns a whole number?

Ex:

if x = number + a remainder then false
20.625 = false

if x = number with no remainder the false

20.000 = true

thanks in advance for any help,
myke

Hi,

Im looking for a formula that will divide a whole number over multiple cells.

For example;

the whole number needs to be divided by 45 with the remainder displayed in the next cell.

It should like this, e.g. A1 (user input) 108, A2 45, A3 45, A4 18.

The number in A1 may be as large as 450, so the formula would have to spread over 10 columns.

Thanks

I have copied and pasted numbers from one program to Excel. The Excel format indicates that the number is in general form. I can change the format. However, the format of the number is not changed. I believe this because when I try to format the number for 2 decimal places it does not format and when I use Vlookup, the match is not recognized.

If I place my cursor to the first of each cell, hit delete and enter, the number does format to a number. Can anyone explain to me what happening?

I need to format 1200 of these numbers. I do not want to do it a cell at a time.

Thanks

Good day, people. I was hoping someone would be able to help me out with a rather simple issue with regards to custom cell formats in Excel. I'm creating a budget sheet for work purposes that needs a breakdown in both Man Days and Crew Days (a crew being six men). These numbers are extracted by calculations on a cell that contains a lump sum number of Man Hours.

Well, what I need for easy understanding/readability for field managers is a format for Man Days that would read "##d ##h". For example, dealing with the number 4782 - 4,782 Man Hours - I'd like the Man Days calculation to display the result as '597d 6h". This is something I can do in actuality but not in a good way. My current method is as follows:

A1: 4782
A2: =(INT((A1/24)*3)&"d "&ROUNDDOWN(MOD(A1,24),0)&"h")

And bingo, it spits out the exact format I need. The problem with this result is it cannot be calculated because it is no longer a valid numerical value, and I'd like to be able to use these values in other aspects of the sheet, such as for SUM formulas. And there are ways around this as well, such as referring back to the Man Hours cell but I'd rather not. I'd like to keep everything neat, in-line, and direct, without overlapping references.

I've tried to correct this using the custom cell formats but haven't been able to get good results. I've tried variations on themese like "##\d .##\h" but that leaves a decimal and I really need the remainder to equate to an actual eight-hour work day. Is there possibly away to make these custom formats handle simple math, like the fractions format? The format "#\d ??/??\h" results in "597d 3/4h", which is kind of along the lines of what I'm trying to accomplish but not quite - however, it's basically performing a division calculation in that format.

Is there a custom format possible that I haven't guessed yet, a way to develop such a custom format, or a way to work the actual cell formulas out so that the result can still be tabulated by later formulas? Thanks in advance, everyone, for having a look at this.

I have a dollar amount I need to divide up amongst people. Sometimes that dollar amount is to be divided 'evenly' amongst them (with the obvious remainder issue presented here) and sometimes some People will be weighted more heavily than others (2 people need to get paid but 1 gets 75% of the amount and the other gets 25%).
Here is an example of what I am trying to do. Notice the first Lot 1 I only have 201 available and the amount being used is 200.99 and in Lot 2 I have 300 and the amount being used is 300.01. I need a formula that will divide the shares amounst the people listed using all lots .

Any suggestions.

Percent of total Remainder tax cost tax cost total number of shares 501 gift Shares total total Jane 171 0.3413173653 68.6000000 0.0100000 102.4000000 1821.1600000 bill 209 0.4171656687 83.8500000 0.0100000 125.1500000 2225.7600000 Joe 121 0.2415169661 48.5400000 0.0100000 72.4600000 1288.6800000 4.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 5.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 6.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 7.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 8.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 9.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 10.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 11.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 12.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 13.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 14.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 15.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 16.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 17.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 18.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 19.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 20.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 21.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 22.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 23.0000000000 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 24.0000000000 0 0.0000000000 0.0000000 0.0000000 0.0000000 0.0000000 UAC Lot #1 0.00010609 0.00 UAC lot #2 17.7847368 Total 501.00 1.00 Shares being used 201 200.99 0.03 Shares used 300 300.01 5335.60 shares total 501.00

All,

Stuck again!!!

I've got a range B3:B267, where previous sheets calculations will place names combined with numbers enclosed in brackets....the brackets are always at the beginning of the cell and there are no repeated numbers.

I have managed to work some code (triggered by the button which will send these calculated values to text and remove the blanks), but I cant seem to get the sort right in column D. What I want to happen is for the values in brackets to go to top of the sort.....and sorted numerically and then the remainder sorted alphabetically. I attach a demo file for you to have a look at.

Thanks for looking.

Skyping