Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

View Answers     

Similar Excel Tutorials

MOD Function in Excel
The MOD function is very simple but it can be used to do wonderful things in Excel. It returns the remainder after ...
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 ...
Easily Enter Fractions into Excel
This is a very simple trick to enter fractions quickly without having to manually format anything. This allows you ...

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


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 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????


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!




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.




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.

Appreciate your help

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.




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

Hi guys,

I want to sort an amount found in cell A2, into columns labelled from B1-E1 that carry various values.
For example if I have an amount of 2000, and I have columns in order from left to right of 100, 500, 1000 and 1500.
I want it to attempt to sort itself from the highest value to the lowest value. So in that case it will leave a mark or the number "1" to represent the amount of multiples in the column as 1 x 1500, and 1 x 500.

Once I have that designed I need it to deal with slightly more difficult numbers. For example if the number in cell A2 was 2250, and I have columns in order from left to right of 100, 500, 1000 and 1500 as before.
It would sort from the highest amount to lowest in 1 x 1500, 1 x 500, and 2 x 100, but the remainder of 50 would not be allocated.
In this case I need the remainder to be rounded up so that it is counted as another 1 x 100, giving me a total of 2300, from my original 2250 value entered.

Any help is appreciated.


Hello All,

I have a value and need to divide it into standard quantities.

Lets say i have a length of 23.5m and i have standard sections of 5m, 3m & 1m.

I need a formula to divide the initial length into the standard sections but in whole lengths ie the "best fit" or match and rounding up any remainder to a whole length.

If it worked out i would like 4 x 5m, 1 x 3m and 1 x 1m.

If the formula could start at the larger size and work down that would be best as it would linit the number of sections.

I have tried roundup and other solutions but failed - please can anyone help.

Thanks in advance..

David


If the time entered in Is it business or personal? & date in b2 matches, then I want a message box to pop up with the word entered on b3. If this is possible then it's gonna be great!!! Please please take a look and help me. Thanks again.

A B C D 1 time 1:40 AM 2 date 7/14/2010 3 Remainder comments Log off



Here is what I dont understand:

A = 19 Mod 6.7

The above example equals 5 per the VBA help file regarding the Mod Operator?????

I'm seeing it as:

19 divided by 6.7 =

19 divided by 7 (MOD rounds up 6.7 first I believe) = 2.714286

Then is said the remainder is all the Mod operator returns, so it should return .714286?

THIS IS ANOTHER EXAMPLE RIGHT OUT OF THE VBA HELP FILE:

MyResult = 12.6 Mod 5 ' Returns 3.

I see it as:
12.6 divided by 5 becomes:
13 divided by 5 = 2.60 - remainder just being .60??

I dont understand how any of this works???
What am I missing?


in Excel, is there a function to obtain only the remainder for a division?



Excel 2010 on W7

Hi,

I have a formula (a price) which needs to be rounded up to the nearest 5000:

P2=IF(M2=0,"",CEILING((Introduction!H$1*M2+M2)-((N2/2)*M2),5000))

I have another cell which calculates my profit:

R2=IF(M2=0,"",Introduction!H$1*'ضد آفتاب (Anti Solars)'!M2)

I need to add the remainder from rounding up P2 to the nearest 5000 to my profit.

In other words, I need to have this amount added to R2:

CEILING((Introduction!H$1*M2+M2)-((N2/2)*M2),5000) - (Introduction!H$1*M2+M2)-((N2/2)

How do I go about that?

adding the above to R2 doesn't do the job.


I am trying to compare a list of names between two sheets. Unfortunately the names are not formatted the same. One sheet has a first name column and a last name column. The second sheet has a full name that includes a middle initial and/or one or more middle names. I am trying to come up with a formula that would return the start of a text string until it hits the space and then another that would return the remainder after hitting the last space. Text to columns doesn't work too well since it is a different number of names for different people. Here is an example of what I have and what I would like it to return. Any help would be appreciated.

Sample data: John James William Doe
Formula 1 result: John
Formula 2 result: Doe


Hello everyone,

I am new at the forum, and have a tricky question.

My company uses UPC codes for identifying its products. As you might know, the UPC codes have 11 digits + 1 digit check code. And the formulation to calculate this check digit is as following:

Step One: From the right to the left, start with odd position, assign the odd/even position to each digit.

Step Two: Sum all digits in odd position and multiply the result by 3. (7+6+1+1+5+2)*3=66
Step Three: Sum all digits in even position. (2+4+2+7+4)=19
Step Four: Sum the results of step three and four: 66+19=85
Step Five: Divide the result of step four by 10. The check digit is the number which adds the remainder to 10. In our case, divide 85 by 10 we get the remainder 5. The check digit then is the result of 10-5=5 .



So, what I did to calculate this was like that:
1. Cell A1 is the eleven digit number: 72641217542
2. Cells A2 to A12 are 7, 2, 6, 4, 1, 2, 1, 7 ,5, 4, 2 correspondingly.
3. Cell A13 has the following formula to calculate the check digit:

=RIGHT(ROUNDDOWN((10-((((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10)-ROUNDDOWN((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10,0))*10)),0),1)


4. Cell A14 has the following formula to combine whole:

=CONCATENATE(A1,A13)


I know it is complicated but it works perfectly. Could you guys advice me any other shorter ways to do that?

Thanks in advance,

Ray


I am trying to place set a formula so that when you place a number in a
column and it is over a set number the remainder amount goes to another
column.



I need a table that will allow my boss to input his total number of pieces order in a table that will divide that number into 3 different styles in 2 colors each (or 6 colors) in 6 different sizes.

The difficult part is I'm trying to divide 200 pieces into 6 different size. How do I set it up so the remainder that can't evenly be divided always goes as whole "pieces" into just one of the columns or "sizes"?