
Divide 2 Numbers And Have 2 Decimal Remainder Format


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Divide 2 Numbers And Have 2 Decimal Remainder Format  Excel

View Answers


Hello all,
Need your help...
My objective is to format the remainder of 2 numbers that were divided to a twonumber 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
Whole, Decimal Numbers & Formatting
 Learn about Whole and Decimal Numbers. See how to write words to represent numbers (for checks). See how formatting can hide decimals. This ...
Decimal Into Fraction
 See how to go from a decimal to a fraction See how to go from a fraction to a decimal ...
Helpful Excel Macros
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
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
6170: 10 free registrations + 40% discount on the remainder
7180: 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!
Problem:
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
2,500.69
340.00
450.20
769.00
241.00
1,345.84
0.20
Required format
2,500.7
340
450.2
769
241
1,345.8
0.2
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?
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 leftmost 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
0A 6G 12N 18V
1B 7H 13P 19W
2C 8J 14R 20X
3D 9K 15S 21Y
4E 10L 16T
5F 11M 17U
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 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 23681140743
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 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 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
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.
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
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
paul.shepherd@nospamparadise.net.nz
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
Code:
=(MID($C2,2,1))*2
Instead of six seperate cells in Row C using MID is there a quicker way?
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'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.
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.
can anyone supply me with some code for a task where a user enters a number say 389. VBA then finds from a list of numbers a number close to the users number but MUST be below it and subtracts it from the users number.
for example.
List of numbers:
25
50
100
150
200
250
300
350
400
so it would select 350, leaving 39 then it would repeat for the remainder finding 25 and leaving 14. as there is no number lower than 14 it would print 375 in one cell and then the remainder over the original number times 100 in an adjacent cell.
One other condition is that before the user enters their desired value, they select how many times it subtracts from the remaining number until it stops and prints the answer.
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?
Thanks,
Leo

