Help.....Formula Required

If current  contents off cell are a variable no from a previous equatio
(eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110)
and � (minus) the lower figure (107) from the higher (110) and =
answer (3) to be displayed in SAME cell

--
gareth6
-----------------------------------------------------------------------
gareth67's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1495
View this thread: http://www.excelforum.com/showthread.php?threadid=26584

0
10/3/2004 9:03:01 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
402 Views

Similar Articles

[PageSpeed] 46

Sorry just realised you asked for nearest multiple of 10. Roundup will
surprisingly, always round up so if you want the nearest multiple jus
use =ROUND() instead

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=26584

0
10/3/2004 9:16:09 AM
=CEILING(A1,10)-A1

Hope this helps!


-- 
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10785
View this thread: http://www.excelforum.com/showthread.php?threadid=265842

0
10/3/2004 11:02:53 AM
If you really mean roundup, then

=ROUNDUP(A1,-1)-A1

however if you mean Round, then

=ROUND(A1,-1)-A1


The difference is that ROUNDP will return 9 if A1 contains 101, Round will
return -1

-- 

HTH

RP

"gareth67" <gareth67.1djmim@excelforum-nospam.com> wrote in message
news:gareth67.1djmim@excelforum-nospam.com...
>
> If current  contents off cell are a variable no from a previous equation
> (eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110).
> and - (minus) the lower figure (107) from the higher (110) and =
> answer (3) to be displayed in SAME cell.
>
>
> -- 
> gareth67
> ------------------------------------------------------------------------
> gareth67's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14953
> View this thread: http://www.excelforum.com/showthread.php?threadid=265842
>


0
bob.phillips1 (6510)
10/3/2004 11:13:40 AM
If you want the answer to appear in the same cell then you need to include 
the original equation within your new equation.

For example my solution to having 107 in A4, with the answer 3 appearing 
would be to put the formula

=10-MOD(A4,10) into the "answer" cell.

However if you want 3 to appear in cell A4 (where your original formula is), 
and assuming that your orginal formula is =SUM(A1:A3), then your formula 
should be

=10-MOD(SUM(A1:A3),10)

Fiona

"gareth67" <gareth67.1djmim@excelforum-nospam.com> wrote in message 
news:gareth67.1djmim@excelforum-nospam.com...
>
> If current  contents off cell are a variable no from a previous equation
> (eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110).
> and - (minus) the lower figure (107) from the higher (110) and =
> answer (3) to be displayed in SAME cell.
>
>
> -- 
> gareth67
> ------------------------------------------------------------------------
> gareth67's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=14953
> View this thread: http://www.excelforum.com/showthread.php?threadid=265842
> 


0
10/3/2004 12:26:59 PM
Fiona,

That actually returns 10 if A1 contains 110, where I would read that the OP
wants 0 in this case.

-- 

HTH

RP

"Fiona O'Grady" <fluffybunkin@hotmail.com> wrote in message
news:cjor76$gte$1@kermit.esat.net...
> If you want the answer to appear in the same cell then you need to include
> the original equation within your new equation.
>
> For example my solution to having 107 in A4, with the answer 3 appearing
> would be to put the formula
>
> =10-MOD(A4,10) into the "answer" cell.
>
> However if you want 3 to appear in cell A4 (where your original formula
is),
> and assuming that your orginal formula is =SUM(A1:A3), then your formula
> should be
>
> =10-MOD(SUM(A1:A3),10)
>
> Fiona
>
> "gareth67" <gareth67.1djmim@excelforum-nospam.com> wrote in message
> news:gareth67.1djmim@excelforum-nospam.com...
> >
> > If current  contents off cell are a variable no from a previous equation
> > (eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110).
> > and - (minus) the lower figure (107) from the higher (110) and =
> > answer (3) to be displayed in SAME cell.
> >
> >
> > -- 
> > gareth67
> > ------------------------------------------------------------------------
> > gareth67's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=14953
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=265842
> >
>
>


0
bob.phillips1 (6510)
10/3/2004 1:18:21 PM
Oops - sorry about that.  Must remember not to try doing maths when under 
the influence!  :)

Fiona
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:eseB2tUqEHA.708@tk2msftngp13.phx.gbl...
> Fiona,
>
> That actually returns 10 if A1 contains 110, where I would read that the 
> OP
> wants 0 in this case.
>
> -- 
>
> HTH
>
> RP
>
> "Fiona O'Grady" <fluffybunkin@hotmail.com> wrote in message
> news:cjor76$gte$1@kermit.esat.net...
>> If you want the answer to appear in the same cell then you need to 
>> include
>> the original equation within your new equation.
>>
>> For example my solution to having 107 in A4, with the answer 3 appearing
>> would be to put the formula
>>
>> =10-MOD(A4,10) into the "answer" cell.
>>
>> However if you want 3 to appear in cell A4 (where your original formula
> is),
>> and assuming that your orginal formula is =SUM(A1:A3), then your formula
>> should be
>>
>> =10-MOD(SUM(A1:A3),10)
>>
>> Fiona
>>
>> "gareth67" <gareth67.1djmim@excelforum-nospam.com> wrote in message
>> news:gareth67.1djmim@excelforum-nospam.com...
>> >
>> > If current  contents off cell are a variable no from a previous 
>> > equation
>> > (eg 107) HOW TO......... round up to nearest divisible of 10 (eg 110).
>> > and - (minus) the lower figure (107) from the higher (110) and =
>> > answer (3) to be displayed in SAME cell.
>> >
>> >
>> > -- 
>> > gareth67
>> > ------------------------------------------------------------------------
>> > gareth67's Profile:
>> > http://www.excelforum.com/member.php?action=getinfo&userid=14953
>> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=265842
>> >
>>
>>
>
> 


0
10/3/2004 1:32:24 PM
This early ... on a Sunday <vbg>?

Bob

"Fiona O'Grady" <fluffybunkin@hotmail.com> wrote in message
news:cjov1s$i15$1@kermit.esat.net...
> Oops - sorry about that.  Must remember not to try doing maths when under
> the influence!  :)
>
> Fiona


0
bob.phillips1 (6510)
10/3/2004 3:34:01 PM
Reply:

Similar Artilces:

Show format in formula bar
Hello: On some spreadsheets I have #'s in the billions added together in cells. Sometimes it is difficult to verify the number is 10,000,000,000 because it shows up as 10000000000. My eyes get crossed counting the zeros. I'd love if the formula bar would show 10,000,000,000; however, can't seem to find a way to do this. Is this possible? No, you can't change the number formatting in the formula bar. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Native" <native_texan_03@yahoo.com> wrote in message n...

Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are related to each other (Cols A-C, D-F, etc...). I have this formula that works well for columns A-C =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B$1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=COLUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B$2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COLUMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B$2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A...

Help with String.Format
Hello, I have a winforms app with some values in the app.config file I use to create a connection string to excel. Some of the values require quotes around the values within the connection string. Here is an example of the connection string: string connectionString =3D @"Provider=3DMicrosoft.Jet.OLEDB.4.0; Data Source=3DBook1.xls;Extended Properties=3D""Excel 8.0;HDR=3DYES;"""= ; Note the quotes around the Extended Properties values. I have the following String.Format code (which doesn=92t work): sConnStr =3D String.Format( ...

Unwanted Formula Display #2
I'm using Excel XP, preparing cost estimates. In my summary page, cell references display the name and total of each category, from the associated category page in the document. When I insert a new line for a new category, and "point and shoot" to assign the formula, e.g., ="A425", it displays the formula rather than the cell reference value, e.g., "Carpentry". I've tried formatting the cell as Text and General, copying another cell reference that is displaying correctly, and then editing the formula, and painting the new cell from one that is wor...

Please help! Outlook not working at all!
Hi! I have been working with Outlook for years - it was always a great feature - but now it is critical since I have an Ipaq that I need to sync to it. Outlook somehow is no longer working/on my computer. I have tried repeatedly to reinstall - to "fix" - anything with NO luck. I get an icon to click on for Outlook and then it goes directly to personal folders (nothing actually happens). Every other component of Office 2000 is working perfectly. Any help would be GREATLY appreciated! I am desperado!!!! Have you run "Detect and Repair" from under help in Outl...

Formula Completion Feature
A useful enhancement for Excel would be a formula completion feature like the one in Delphi. You just type an equal sign, and a list of valid labels, names and functions pops up below the cell. There should be different colors for the text telling what the word on that line is, i.e., label, name or function. As you type, all the entries not matching the text you have typed should disappear. One could move up and down the list by the up and down arrow keys. To accept an entry one could just type a comma (if you have completed a function argument), Enter (if choosing a label, name or func...

Match() - Offset()
Often I see mention of Offset(), Match(), & Index() XL functions. Could someone big-picture these functions for me? I have not used these functions yet in data lookup or evaluation. Shame on me. That said, The concept of "Index" I understand from db work. "Match" seems vaguely logical to me for "lookup" work. "Offset" means nothing to me yet. Reading the XL help or reference books do not give me typical uses and why's of the functions. My focus is the accounting/financial world. Using XP Pro & XL XP Pro SP1. Dennis This file b...

WHAT DOES THE SYMBOL"$" REPRESENT IN A EXCEL FORMULA?
SORRY FOR THE NOVICE EXCEL QUESTION - IM JUST LEARNING THE PROGRAM AND CANT SEEM TO FIND IN "HELP" ALL SYMBOLS FOR FORMULAS It's used to create an absolute cell reference, i.e. one that will not be changed even when the formual is copied and pasted. Without the the $ symbol, a formula such as =A1+B1 in cell C1 would be changed to =A2+C2 if it was copied into cell C2, whilst this is useful if you're creating a table of information, it can some times be a pain. By changing the original formula to =$A$1+$B$1, it will not change when copied. The above example keeps it en...

Help with my Excel Database
The following is what I need help with: (1) I have a main sheet where I am entering my data. I have defined a dynamic range for this data so that if I enter a new row or column in the sheet then this data will be included in the dynamic range. I want to know what I need to do to copy the data from the main sheet into asecond sheet so that when I enter a new row/column or amend the existing data in the first sheet then the second sheet will automatically update. I presume it is something to do with referencing the dynamic range from the first sheet into the second sheet. ...

HELP PLEASE with Outlook Message
WhenI send and receive mail a message pops up on the sreen saying: Contacting then server for information. It then runs the green bars kinda like it is completing something and goes away. My email seems to be working ok but this just started. It is slowing me down very much. Has anyone information on this? All comments are appreciated very much. Keith Is it only on reading messages or elsewhere also? "Keith" <keithgirdley@blomand.net> wrote in message news:JOWdnej4T-nnY7jUnZ2dnUVZ_tHinZ2d@neonova.net... > WhenI send and receive mail a message pops up on the sreen s...

Help Text for Radio Buttons
I am trying to create a survey form with multiple choice radio buttons. I want to add descriptive text that appears when you float your cursor over the button (like that for a toolbar icon). I've scoured Excel help and couldn't find anything. I don't have experience with macros or Visual Basic but am willing to try if someone can hold my hand through it. If the radio button is in a cell, add a comment to the cell. Then the instructions will appear when the mouse hovers over the cell. -- Damon Longworth Don't miss out on the 2005 Excel User Conference Sept 16th and 17th...

Formulas not calculating properly
I need help! I created a worksheet before with formulas and I protected the sheet. I made it so that rows can be added and numbers can be inputted and the formulas will still sum the numbers. It worked fine until today. Now, on the status bar, it shows Calculate and it doesn't add any new numbers inputted. I have to unprotect the sheet and F2 the formula and enter to update the sum. Why is this happening? Thanks for any help. Sounds like you are manual calculation mode. could be the result of several things. Try going to Tools--> Options-->Calculation and see if it is...

help needed for closing modeless dialog
hello, I have a dialog based application. Inthe OnInitDialog() of this dialog i am poping up a modeless dialog.Now i am placing a menu in the parent dialog.what i want is that when i close the modeless dialog by clicking the red cross button on right hand corner then only i should be able to pop up modeless dialog from the menu.Do i need to handle the WM_CLOSE;if yes how? It could also be sufficient is closing makes dialog invisible and then menu option makes it visible...... thanks in advance When the X is clicked, a WM_SYSCOMMAND message is sent with the ID SC_CLOSE indicated....

Macro Help 05-20-10
how do i assign a value of a cell to a variable in a macro This sets the variable: Sub dural() Dim r As Range, v As Variant Set r = Range("A1") v = r.Value End Sub to set the cell, reverse the equation. -- Gary''s Student - gsnu201003 "BigO" wrote: > how do i assign a value of a cell to a variable in a macro ...

lookup formula #5
I am trying to come up with a formula that looks up an order number (eg 2013864) on a list on another sheet (2)and then returns all the listings corresponding to that number eg: Sheet 2 Order No.s Details 2013864 red 2013864 blue 2013864 green 2013865 red 2013865 green 2013864 black Sheet 1 2013864 (this is where I enter number to be looked up) red ( details that ) blue (correspond to) green ( order No ) Regards gregorK The easiest way to go i...

need help simplying my formula..
for cell D25: =IF(A25=0,0,IF(RIGHT(B25,4)="Road","Road",IF(RIGHT(B25,6)="Bridge","Road",IF(RIGHT(B25,7)="Highway","Road")))) this means that, B25 could be "Bay bridge", "County Road", "Blue Highway", etc etc and the result for D25 would be "Road" is there a simpler way of writing this formula? 2nd question: for cell B5 IF(A1=0,0,IF(C2=4,IF(A1=5,5,IF(A1=6,6),A1))) can i write this any simpler? tia One way for the first part: =IF(A25=0,0,IF(OR(RIGHT(B25,4)="Road",RIGHT(B25,6)=&qu...

Help with XslCompiledTransform
I am using XslCompiledTransform to transform XML into Excel 2003 SpreadsheetML. In order to get the resulting XML file to open in Excel, we need the following two lines at the top of the file: <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> I can get the <?mso-application ...> line by using an <xsl:processing-instruction> element, but for the life of me I can't get XslCompiledTransform to include the basic <?xml...> line. When creating the XmlWriterSettings for use in the transform, I have not altered the OmitXmlDeclarati...

Help! IF function is too limited
Hi! I've created a drop-down list of cities in a cell, under which there are two more cells to be filled out with the address and zip codes corresponding to each of the cities. How can I do this, knowing that the original list is a three-column list made of city-address-zip? -- Ringo ------------------------------------------------------------------------ Ringo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27502 View this thread: http://www.excelforum.com/showthread.php?threadid=470178 Use VLOOKUP into the original; list using the DV value =VLOOKUP(B...

Help mfcdll not working in vb
I am trying to write one MFC dll. I have created one test function declared like this in the cpp of dll _declspec(dllexport)void test() { AFX_MANAGE_STATE(AfxGetStaticModuleState()); AfxMessageBox("Hello"); } the problem is that this dll works fine when i use it in vc project but in VB it gives the following error Can't find dll entry point test in test.dll However when i declare this function in test.def it starts working in VB but gives an error in VC. I am not able to find out the reason for this behaviour. I will be thankful for any help on this. Sumit Marwal ...

SMTP Message Queue full. Help!
Exchange2k3 SBS. No email was going in or out so I took a look at the queues in system manager. I found a whole bunch of emails that were stuck and appeared to be coming from the postmaster account. Is my server a victim of being a spam relay? When I run relay tests it says that my server is not a relay. How do I get rid of all these bogus queues? I know I can delete the messages but its the queues I want to remove. the queues cannot be deleted. those queues are created on demand based on the email destination. What you need to do is to find out what is causing those mails and solve th...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

Help with SUMIF function
Hi, I'm trying to add up a range of cells based on two IF statements, but with no luck :( What I was trying to do was: =Sum(If (G3:G2224,"Home")*(M3:M2224,"Blue"),P3:P2224) This doesn't call up the result that i want,which is the sum of the P cells if range G = home and range M = blue. Can anyone suggest a work around or point out to me what I am doing wrong? All help would be very much appreciated! -- Regards, Dan Hi Try =SUMPRODUCT((G3:G2224,"Home")*(M3:M2224,"Blue")*P3:P2224) -- Regards Roger Govier "Confused Dan"...

Who is This STEVE... ? Does he really help.. Others
I wonder Who is This STEVE... Many question for learning purpose I go His name Pops up.... and I always see.. "Help .. with $$$$$$".... We Leaner.... Well Already spent Money for Books... "Ahmed" <khalakmu@hotmail.com> schreef in bericht news:27B4D66C-3FA3-45E2-A98C-087E7C12A1BC@microsoft.com... > I wonder Who is This STEVE... Many question for learning purpose I go His name > > Pops up.... and I always see.. "Help .. with $$$$$$".... > > We Leaner.... Well Already spen...

Help in turning off underlining and blue font
Hello, Does anyone know if there is a way to turn off the automatic underlining and blue font of the text when entering a email address in a cell? Thanks, Bob Tools / Autocorrect options / Autoformat Tab / Replace Network paths with hyperlinks - Deselect it -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ----------------------------------------------------------------------...

Help with counting formula
Sheet 1, Row 1, Column 2 - opening hours divide into 15min segments. B1 - 08:00, C2 - 08:15, D2 - 08:30, and so on. Sheet 2 , Columns 1 and 2 - a list of each agents break times. Column A - Start time (eg 10:00) and Column B - End time (eg 10:30). Sheet 1, Row 2, Column 1 - summary titles. (eg Cell A2 - "Morning Break"). Problem: I need to summarise the number of agents away on, say, "Morning Break" per 15 min segment. So, B2 must count the number of people on "Morning Break" at 08:00, B3..the number of people of people on morning break at 08:15, a...