Use Plus Minus Variables
Nov 30, 2008Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page
View 2 Replies
ADVERTISEMENT
Unzip Code - Works Without Variables, Breaks With Variables...
Feb 5, 2009Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
+ 0.1 Minus
Jan 20, 2010I have part if the code here’s
PHP
Range("f" & indexI + 1).Value = Format(Strl, "00:00")
to return value = time formats
How do I modify it to + 0.1 minus?
Sample
IF the return value = 12:00:00
Now I wanted = 12:00:01
Tan To The Power Of Minus 1
Dec 10, 2008How can i produce "tan to -1 " in excel ?
View 9 Replies View RelatedDate Minus A Day
Dec 16, 2013I use a formula that has a date in it 11/15/13 for example. Then I have a formula that calculates "days" (15), but I want to subtract one. How do I do that?
View 2 Replies View RelatedGreater Than Or Less Than (Plus Or Minus)
Jul 19, 2006Here is my formula.
=If(Sheet1!J5=$J$4,0,(Sheet1!$J$4-Sheet1!J5)*Sheet1!$J$1)
It is perfect, except it doesn't have one final step. What I need it to do
is be able to do that ONLY if it is greater than or less than by a specified
amount. So there needs to be a modification of ... Sheet!J5<> (but by 100 or
any other number that I set) $J$4,...
Difference Between Two Days And Minus A Day
Jan 3, 2014Assuming the first date is in A1, and the second date is in B1, in standard dd/mm/yyyy form, my current formula is =B1-A1-1.The '-1' is due to the fact that if a patient stays for 10 days, they will only spend 9 nights in the hospital. (Bed Nights).The problem is, the formula is stretched in the total column from, say C1 to C50. Each one of these has, or will have, a number of days in it.However, due to having th '-1' in the formula, empty rows that are yet to have a patients details inputted have a -1 where I need a 0. The only reason I need to change this is because I need a running total of the bed nights of all the patients.I think the formula I'm after is something along the lines of; 'If cell B2 is empty, input 0. If B2 has a date, use formula 'B2-A2'
View 9 Replies View RelatedPlus Or Minus Sign In The Beginning?
Jul 29, 2008I want to save phone no as +99 9876543210 in excel 2003 on my xp pro machine. But if i give a + sign in the cell, some blue dotted rectangle shows up and everything messes up.. I think it is treating it as a formula or something... how can i save this in the cell. tell me in detail if you are going to tell me about macros or vb code as I don't know how to insert code or program macros.
View 3 Replies View RelatedCheck For Plus/Minus Condition
May 31, 2007With out using Conditional Formatting, how can I check a cells value if it can fall within a range of +-.002?
I have this code but it seems to have limitations.
Average Last Five Numbers Minus The Low And Hi
Apr 23, 2009=AVERAGE(INDEX(E5:IV5,LARGE(IF(E5:IV5<>"",COLUMN(E5:IV5)-COLUMN(E5)+1),5)):INDEX(E5:IV5,MATCH(9.99999999999999E+307,E5:IV5)))
I have this formula where it averages the last five numbers in a collumn, but I want to average the last 5 numbers less the max and min number.
Show Zero If Amount Is A Minus
Nov 19, 2008I need a formula that that will show £0.00 if the amount is under zero, i have a formula k15/o15 that gives me a amount but if this is under zero i need this to show as zero not -£70 for example.
View 3 Replies View RelatedDiplay Minus Hours In Red
Feb 20, 2009I am using time based on the 1904 calendar, this is so time can be shown as a minus figure when to cells are taken away and the result is say -17:25. What I want to do via conditional formatting is show any minus hours as red. Tried various options but I have had no luck so far. or can this be done via VBA?
View 6 Replies View RelatedAverage Minus Most Recent Row
May 4, 2009I have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week.
Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.)
However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average.
So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()"
ie - column could be ......
Minus Percentage In Formula?
Aug 28, 2002I need to write a formula to minus a percentage of a total.
for example I want to take away 20% of the total of £8.75.
Workbook Name Always Changes By (minus 2 Business Day)
Feb 13, 2012My file name constantly changes, it will always be (minus 2 business dates from today)
Now when my macros run it goes between different worksheets so I need to tell it to go to right workbook. I tried below but getting an error 438 'Object doesnt support this property or method'
Code:
Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate
Sheets("data").Select
Select USED Range Minus 1 Row
Oct 10, 2008I need to sort some data on a spreadsheet but I am uncertain on how to select the used range.
I just want a piece of code that will select WHATEVER area of the sheet is populated with data. The sheet I will be doing the selecting on is titled "Master".
To add to it I DO NOT want ROW 1 included in the selection.
How I Can Minus Number From A Month?
Dec 2, 2008In an excel file i have two fields,
Month to Start - is a combobox with 12 months, next is
Month to go back - is a textbox, here specifying number of months to go back.I need to store month names by minus the existing month - number in a textbox
eg: if Month to Start is 'September' and Month to go back is 2, then what i need is September & August in an array or separate variables.
How I can minus number from a month?
Change All Values To Minus
Jun 11, 2009I have a spreadsheet which contains values and text. Would I be able to change selected values to a minus value by clicking one button?
A2 = 200
A3 = 300
A4 = 1000
B3 = 50
C5 = 600
Changes to
A2 = -200
A3 = -300
A4 = -1000
B3 = -50
C5 = -600
I would also like the option to change them back again as well.
I have about 40 values so do not want to do this manually.
Macro Minus Numbers
Jul 11, 2009How do i stop changeCell from going into the minus?
Sub goalSeekSample() Dim seekValue As Double Dim changeCell As Range Inflation = Range("O7").Value seekValue = InputBox("What's the value to seek?") For Each changeCell In Range("N18:N32").Cells changeCell.GoalSeek Goal:=seekValue, _ ChangingCell:=changeCell.Offset(0, -4) seekValue = seekValue + (seekValue * Inflation) Next changeCellEnd Sub
How To Show Minus Value In A Formula As Plus Sign
May 31, 2014I have attached here an excel sheet with some data. I need to show the minus value in D5 as a plus sign, is there any conditional formula to work this out??
View 4 Replies View RelatedTodays Date Minus 1 Month
Nov 20, 2008I am trying to write a formula which takes todays date and goes back 1 month.
Eg Today = 20/11/2008, however my formula would give me 20/10/2008
Subtracting Dates (minus Weekends)
Apr 20, 2009I attached an excel file for an example. Basically I am calculating on time shipping, but I want don't want Saturday and Sunday to coun't against us.I need G5 to equal 3 days. Right now when I subtract E2 from F2 I get 5 days. But we only work M-F, and Saturday and Sunday shouldn't count against us. How would I subract the days automattically, so it takes out Saturday and Sunday?
View 10 Replies View RelatedMultiplication To Ignore Minus Numbers
Oct 7, 2009If I wanted to get a cell, say A1, to multiply a value, for example 7, by the results in another cell, say B1, but NOT multiply if that cell's value is a minus number, is there a way of getting Excel to do that?
Normally I'd just type =SUM(A1*B1)*7, but how can that be reconfigured to ignore minus numbers? e.g. if B1 contained -5, ?
Total Time Minus 8 Hours?
Jun 24, 2013I have a time calculated and I want to know, Out of that time how much is above or below 8 hours.
Ex. 1: 7:30 - 8 = [result] "-:30" mins
Ex. 2: 11:00 - 8 = [result] "3:00" hours
OR
How can I convert 7:30 into 7.5 [decimal] and then subtract that from "8" to get the difference?
I know this is an easy one, I have done it many times before. It's crunch time and I am drawing a blank.
Minus Figure Wont Calculate
Dec 13, 2006I have been creating a new spreadsheet in work to show materials variances
I am having a problem with a minus figure in a calculation as follows
expected opening stock = (172)
Actual opening stock = 1022
My return cell has the following formula (Actual Opening Stk - Expected opening stk)
I should get the answer of 850 - I am actually getting 1194
Conditional Formatting And Minus Figures
Feb 28, 2007I would like to put a format on all cells that are below -15:00 (that's the
cell format).
So, e.g. -20:00, -50:00 but NOT -10:00.
Do I use greater or less than? I cant seem to get any to work!
I have already put a format on the cells for greater that 15:00 which works as it's whole numbers - although it has changed the 15:00 in the conditional formatting cell to 0.625 but still seems to work?
Calculating Dates Using Plus Minus Days
Oct 23, 2007I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
View 9 Replies View RelatedDd/mm/yy Minus Yymmdd (formatted As General)
May 17, 2006I use the first six numbers of a SA Identity number to calculate the age of a person as these six digits are actually the year (yy) followed by the month (mm) followed by the day (dd), I am born 01 September 1962, and therefore my first six digits are 620901.
Assume the figure 620901 is placed in Cell A1
Now, in another cell, say A4, I have a future date, but this value is formatted as dd/mm/yyyy. Assume this value to be 01/10/2017.
Firstly, I'd like to know How old I AM at that date and secondly, just because I am battling so, how old I will be on my next birthday, because adding the figure 1 to a total has never been so useless - it just doesn't work! I tried adding all sorts of numbers for months and days in a year but there was no consistency.
So here is my question: Simple - how do I get this to work?
On 1/10/2017 I will be 55, or turning 56 At Next Birthday. I have the following function that gives the answer of 55, but not 56 ANB even after 1 additional year is added to the function (the cell is formatted as yy):
Cell A6 Function = A4-DATE(LEFT(A2,2)+1900,MID(A2,3,2),MID(A2,5,2))
Also, ON my birthdate, 01/09/2017, it says that I am 54, and it has to be wrong because I would have turned 55, unless the function uses time and not just the actual date somewhere. On the next day it does, at least, see me as 55.
how to add calculate what my age will be AT MY NEXT BIRTHDATE for any given date in A4?
Trim Left And Minus A Character
Jun 29, 2007Sub TrimSlashesToRight()
Dim cell As Range
For Each cell In Range("AN2:AN2000")
cell = Left(cell, InStr(cell, "//") -1)
Next
End Sub
I cannot get it to run this, it always crashes on
cell = Left(cell, InStr(cell, "//") -1)
It will work fine like:
cell = Left(cell, InStr(cell, "//") + 0)
but then it leaves the first / in place, i want it to start deleteing before both //.
Find Formulas Ending With Plus Or Minus X
Jul 11, 2007I 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.