Free Microsoft Excel 2013 Quick Reference

number = another number plus or minus 0.5 in vba help!

Hi all,

Only in vba, variable is angle as double
and match as double

how would i say

if angle = match + or - 0.5 then

so it compairs angle to match and all the values between (match -0.5) and (match+0.5)

How does one right this in vba?

Thank you

Post your answer or comment

comments powered by Disqus
I have the following formula:


and I want to add a part to the formula that adds the following condition:

D4 = BK4 plus or minus 5

For example if BK = 100 I want the additional condition to be true if D4 is in the range 95-105 but I am not sure how to write this.

I have to setup a girth gear on a large ball mill. before starting I need to check the mounting flange run out using two (2) dial indicators, A and B.

The readings from the dial indicators can be plus or minus depending on the direction of movement of the dial indicator.

The general rules are:When the stem of indicator A is pointing or moving in the same direction as indicator B, subtract the values entered in B from A

and When the stem of indicator A is pointing or moving in the opposite direction as indicator B, add the values entered in B to A

Just repeating that the value A or B can positive or negative.

So what I am after is a guide to show me how i can calculate the values for A and B when the indicator move in the same direction and also calculate their values when they move in opposite directions. For example

Indicator move in the same direction A= 0.2mm, B = 0.4mm = 0.2 - 0.4 = -0.2mm
Indicator move in the opposite direction A = 0.2mm B = -0.6mm = 0.2 + -0.6m = -0.4mm

Marcel with thanks

I cannot find a posting relative to this though I think I've seen it in past so here goes "again" if already out there and pardon my not finding it if so.

I want to make my percent results appear as plus (+) or minus (-), for example, the rate of increase over the last period was plus 10 percent, ie, "+10%" instead of "10%". Excel already takes care to show a minus sign, eg, "-10%". I think it's done in Format Cells/Custom.

Thanks in advance for the help.

I am having trouble programming a Plus or Minus GPA calculator on excel. I learned that I can assign a numeric value to a letter. for example, if I input "=A" i will get "4.0" as a return. However, the define name function does not let me input a grade with a minus sign or plus sign. eg. "B+ or "C-".

Does anyone know what I can do about this?


I have a file outputted to excel that has in the far left column, left of
the row numbers, a plus or minus sign that seems to group about 3 rows every
so many rows. Is there a way I can clear all this grouping? What is it?


--Randy Starkey

Hi. I can only do the basic stuff with Excel so I hope to get help from the advanced Excel users in this forum.

I work with textiles, clothes in particular, and it's production includes obviously measurement specifications for body length, sleeve length, chest, waist, neck widths and so on...with grading for every different sizes (S, M, L, XL), besides materials and acessories specs, etc.

It´s very common to have these size measurement charts in excel (see attached Report) and the differences found when checking samples for approval or production, are noted down for reference and eventually to determine corrections to be made.

I recently started to try Nuance Dragon Naturally Speaking (DNS) to dictate numbers into Excel.

Using DNS, I tried and succeeded to input in Excel the differences found when measuring garments, by dictating plus or minus 1 or 0.5 or whatever difference found in relation to spec, but the process was complicated because I allways had to dictate the difference found + "press enter" everytime in the end to enter data in the cells.

Then I tried and succeded with DNS command scripts, but it was also complicated as it meant having a different script assigned for each of the typical differences (plus or minus 0.5, 1, 1.5, 2, 2.5, 3...) and executing according to what was found.

Finally I thought: the best way to do it would be dictating the exact measurement found and have some sort of script or macro to account the difference between spec and measurement found and "enter" the plus or minus difference in the cell, even if it was + 0.8 or - 1.3.

I imagine this could be done by having a formula on the cells depending on a variable value. The variable value would be the exact measurement given by the DNS dictation and the formula would then execute and move to the adjacent cell down for the next measurement.

Example on Report: chest width, size S is defined 44. Cell G8 would have a formula like =(F8-V), variable being V. If we got 45 as real measurement than V=45 and that would make cell formula =(44-45) result -1.

Typing or dictating 45 is not the biggest issue here, so it remains a pure Excel problem so far. How to get the + or - result value in a cell, inputting the real value found to replace the variable V and make formula execute.

45 is in fact + 1 in relation to spec 44 so, this means that all minus results should become plus signs and the opposite is also true. If real measurement was 43, then, result of formula (44-43=1). Positive 1 meaning in fact -1 in relation to spec.

By the way, I need to have the + and - signs together with the number in the cells and the way I found it to work was formatting the cells where the differences have to be filled in, as text (columns G,H,J,K,M,N, P,Q).

I do not know if there is a better way, or if this complicates things because the method I am describing means having a calc formula in those cells.

Again, I repeat: although my plan is to dictate the values they could be typed as well. How to solve the Excel part is the problem for me.

Can anyone help me out on this? Any suggestion or hints to try.



dear sir i want to learn how to plus or minus hole sheet in excel

example there are 500 sels and all are in three digit i want to plus 25 in
all ho can i do this

Happy New Year,
Can anyone help me to formulate a formula that is TRUE if the less than or equal to is plus or minus one?


Thanking you in advance for any and all help.

How do you Round up to the nearest 0.5 (ie: 0.5, 1.0, 1.5, 2.0, etc) using VBA code?

Round((TextBox1.Value * textBox2.Value),1) doesn't work.

I have a long list of dollar amounts and need to figure out which ones add or subtract to another number. For example, I have a list of the numbers 1, 10, and 100. I'm trying to find the number 99. I want to be able to input the three numbers in column A and put 99 in column B and have it tell me that 100 - 1 = 99. This is a simple example, but I typically have lists of about 30 numbers, sometimes up to about 200. The answer I'm looking for would normally be two numbers plus or minus each other, occasionally 3. I wouldn't need to find combinations of 4 or more. If there were more than one answer, I would need to know all the answers. Can anyone help me please?

I think this is an easy one but beyond me! How can I format the cell
to show the result of a sum as a plus or minus (or positive and
negative)? It shows minus values by default but I cannot get it to
show a plus sign. I have tried the Format Cells/custom part with no
joy. example:

Cell B8 = 3
Cell C8 = 2

=SUM(B8-C8) gives me a "1" in cell D8, but I want it to show "+1"


Cell B11 = 4
Cell C11 = 5

=SUM(B8-C8) gives me a "-1" in cell D8, which is correct, however I
want this to appear in red

Grateful for any assistance.

Thank you.

ivoryhunter's Profile:
View this thread:

as they take pH measurements from hundreds of tests. I have a spreadsheet
template that multiple employees enter data every other day. Once the data
is entered the template is saved as an xls file and the name of the file
includes the date the data was entered. What I would like is another column
so that when specific data is entered it is compared to value entered two
days previous so that the employees can tell if the current value is
approximately the same, within a certain range. As a simplied example shown
below, I have six samples shown in column A and the pH value is entered in
column B, and the file is saved as Data_19_June_05.xls. As the employee's
enter data for June 21 (file name Data_21_June_05.xls) I need them to know
if the value is plus or minus 0.1 from the value on June 19. So for Sample
1a the pH value measured on June 21 and typed into the spreadsheet should be
2.5 to 2.7 and 2.8 to 3.0 for Sample 1b. I can easily compare data from one
day to the next using links, but the problem is these would be hard links,
which means when entering data on June 23 (file name Data_23_June_05.xls) the
links would still compare to the data from June 19. The template needs to
change the links automatically to the file from two days ago. Can this be
done with formulas or can a macro accomplish this?

Example data in file Data_19_June_05.xls:

Sample pH
1a 2.6
1b 2.9
1c 3.4
2a 2.8
2b 3.1
2c 3.5

I think this is an easy one but beyond me! How can I format the cell to show the result of a sum as a plus or minus (or positive and negative)? It shows minus values by default but I cannot get it to show a plus sign. I have tried the Format Cells/custom part with no joy. example:

Cell B8 = 3
Cell C8 = 2

=SUM(B8-C8) gives me a "1" in cell D8, but I want it to show "+1"


Cell B11 = 4
Cell C11 = 5

=SUM(B8-C8) gives me a "-1" in cell D8, which is correct, however I want this to appear in red

Grateful for any assistance.

Thank you.

I am trying to make something relatively idiot-proof as part of our decision-making process. I started putting together a basic format of the user interface (which is shown below). People will input a date in A5 and a value in B5. C5 and D 5 are formulas that will make some changes to A5.

What I want is a macro that will look at C5 and D5 and will return all records (from another sheet) that are +/- 0.5 from C5, but only if the maximum value for each record is equal to D5. The records would then but copy and pasted into the results area beginning in row 15. I would like to limit the copy and pasting to a certain number of columns, but if I have to take the entire row from the other sheet, thats ok.

Ill post some of the info from the other sheet in my next post so you can have a better idea of what I am workign with.

******** ******************** ************************************************************************>Microsoft Excel - Outstanding Bullet Debt.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC5D5=
ABCDEFGHIJKLM1INPUTS Notes: 2 Look at maturity date 3Advance Return all debt where term = +/- .5 from term of new advance 4Maturity DateTCOFTermGapMacro Button As long as gaps are equal 512/19/20062.5 1.18 2 6 7 Probably need loop macro 8 Copy and paste from Debt tab to User Interface tab 9 Repeat till end of list 10 11 Copy either entire row, or only data to Col R 12RESULTS 13 14Ref #CusipPortfolioDescriptionPoolSwap Number Outstanding Balance RateMaturity DateCall DateSettlement DateSwapped?Clean CUSIP15 16 17 User Interface
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Does anyone know the best formula to use for rounding to the nearest 0.5? The numbers I'm using refer to kilograms and I want to round to the nearest 0.5 kg.

For example, the number
112.2 kg - I want to round it as 112.0kg, and
112.3 kg - I want it to round to 112.5kg

I've tried the FLOOR and CEILING functions with a 0.5 Significance, but they either round both up to 112.5 or round both down to 112.0.

Any ideas are most welsome!


I received a spreadsheet that used easy ways to show and hide rows and
columns by clicking on numbered little boxes or plus and minus signs. These
are located above the column headers and to the left of the row numbers. The
boxes are very small numbered boxes that when clicked hide and unhide a
predetermined section of the sheet. I would like to know how to do this.


I am using Excel 2003 SP1.

I am entering the number of hours it takes to do a task into a cell as 1.5

I want it to display as 01:30 ( 1hr:30min )

I can do this by entering 0.5 in one cell and then converting it to a text
01:30 but I would rather loose the additional coloum to do this by just
formating the cell.

Can this be done some how

The report that i have attached is a monthly report that i am try to track details for agents Log in and log Out and the breaks. What I am trying to do here is a to insert a button to run a macro which gives a snapshot for each agent. I am trying to find out how many times there exceeded the break and how many times the did not log in and log out correctly.In case of Log in and Log out 2 mins Plus or minus is ok.

Can somebody help me on this new task??????Thanks In advance

Hi all.

I have many regularly used workbooks with financial data in them. Many times the financial data needs to be adjusted due to rounding issues. To do this I add a +1 or -1 or sometimes it is in decimal form +/- .1 Also the number "1" is not always (but almost always) used. There are some circumstances where I may add + 2 or +6 or any small number to the end of a formula.

My problem is that when I use the file again after making these changes, I am wanting to remove the "adjustments" I have made to the formulas. Sometimes a green triangle will appear in my cell telling me the formula does not match that of other near-by cells, and that is good, but it is not consistent enough to find them all, or even most of them.

What I am wanting is a macro I can run that will identify any cell with these "adjustments" I have added to them and I will then go in and delete the +/- 1 or whatever the number is. I can easily do a Find "+1" for the entire workbook, it is the range of possible numbers that is preventing me from doing a simple ctrl + f.

Any suggestions appreciated

Hi all , thanks to the Oheozz for last formular , i have in col B the date , col C person , col D distance , col E im wanting to get distance change from there previous entry , eg , B17 Ringo C17 2100 - Ringo C16 1850 = in E17 +250 , John C15 1800 - John C14 2000 = -200 . George C7 2100 - George C5 2100 = 0 . So in col E i would have +250 , next row -200 , next row 0 , thanks in advance , see sample sheet . Kevin . R/C DateA PersonB DistancC Distance change +/- E
2 1990-12-10 John 1200
3 1990-12-11 Paul 1400
4 1990-12-12 George 2100
5 1990-12-13 Ringo 1600
6 1990-12-14 George 2100
7 1990-12-15 Paul 1200
8 1990-12-16 Ringo 1400
9 1990-12-17 John 1400
10 1990-12-18 John 1600
11 1990-12-26 Ringo 2000
12 1990-12-26 George 3200
13 1990-12-26 Paul 1100
14 1990-12-30 John 2000
15 1991-12-30 John 1800
16 1992-12-26 Ringo 1850
17 1993-06-26 Ringo 2100

I need help with rounding an answer to either a whole number or a .5 I know
this can be done in Excel and there are limitations with the Round() function
in VBA. I've seen replies stating to use the application.round but how would
I incorporate that into mine? Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
' This prorates the # of months'
On Error GoTo ws_exit:
Dim ans As String, DteDiff As Integer
If Not Application.Intersect(Target, Range("$C$25:$C$54")) Is Nothing Then
If UCase(Target.Value) = "Y" Then ' allows for Y and y
With Worksheets("agreement")
ans = InputBox("What is the warranty expiration date #", "Prorate
months", _
DteDiff = (Range("$L$11") - DateValue(ans)) / 30.42
Target = DteDiff
End With
End If

Hi, I would like to calculate the difference in time. The problem is I want the time to show has a plus or minus figure but in the format off 0.10 or -1.30 etc. An example would be:

Cell 1 Cell 2 Cell 3
23:00 00:09 1:09

Cell 1 Cell 2 Cell 3
00:04 00:02 -00:02

I can get it to work with two calculations but I want it to work in the same one and the colour of the cell to change colour if the value is between or less than a time which I know how to do

Thanks Neil

how do i round to the nearest 0.5

trying to find a formulato add or minus a percentage from the total

No luck finding an answer? You could always try Google.