Each cell below contain a number and a time + ET. if like say i want to delete the time + ET and leave only the number inside all cell.
1.1425 8:20pm ET ON A1
3.0805 6:10pm ET ON A2
814.30 6:42am ET ON A3
798.70 11:27am ET ON A4
380.00 9:10pm ET ON A5
How do I change a Positive number into a Negative number inside a cell formula?
I have a figure in Cell AE 101 which has the positive number 141
I have another cell W1 which has the positive number of 437
I want to create a formula in cell X1 that takes the number in cell AE 101 of 141 and convert the number inside the formula of 141 into a negative number of -141 and then subtracts that number from Cell W1 of 437 leaving an equation inside the formula to read. 437-141 = 296
Since the number in Cell W1 changes all the time and the number in AE101 Changes all the time the new formulated number in X1 will change all the time too as the cells get updated.
How can I drop the whole number part of a number and leaving only the decimal part of the number. Then multiply the decimal part of the number with a number. Then repeat this in a sequence. The object is to convert Lat and Long decimals to Hr. Min. Sec.
eg. 53.535663 .535663*60=32.13978 .13978*60=8 53 32 8
eg. 113.352640 .352640*60=21.1584 .1584*60=9 113 21 9
eg. 113.306579 .306579*60=18.39474 .39474*60=23 113 18 23
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
View 1 Replies View RelatedI need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
Is there a way that I can remove the whole number to leave the decimal place.
1.69 should be 0.69
10.71 should be 0.71
0.48 should be 0.48
Due to work, I need to creat the captioned file for our company.
View 2 Replies View RelatedI was created an annual leave planner and I would like the box in Colum A to reduce the number of days they have left every time they book leave. I would like it to start off as 25 days leave including UK bank holidays.
View 4 Replies View RelatedI created this formula =G2*2.9%+.3
what I am trying to do is take the number in the G column multiply it by 2.9% and add 0.30. For instance if 20.00 is in the G2 cell, the number I want the formula to produce is .88
the formula works for me but what happens is the rest of my sheet that does not have any numbers in the G column gets filled with .30
How do I prevent the formula from calculating if the G column is blank?
I have a spreadsheet that contains given answers to a multiple choice test. I want to count the number of times each possible answer has been chosen at the bottom of the column. I have tried to use COUNTIF and that works fine to give me the number times each answer has been chosen but there is just one annoying thing. If the given answer hasn't been chosed by anyone, a "0" is automatically entered into the cell. This tends to really clutter up the spreadsheet and I would prefer for the cell to be left blank if the answer hasn't been chosen by anyone.
The closest I can come up with is: {=IF(D1:D10="","",COUNTIF(D1:D10,"A"))} but unless the answer "A" is chosed in D1, the cell remains blank.
If "A" is chosed in D1, then the formula works and counts all the rest of the cells that have "A" as an answer.
I know about
(isnumber(match({,12054, 12056, 12058}).....
How could I express a range of numbers to identify 12054-13001. How would that expression be written?
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder)
The name of the templates are: "Standard" and "Other".
I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done.
One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
I have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How is it possible to leave a cell in a set color, if nothing is entered in the cell? And is it possible to merge it with it with existing formulas allready in the cell?
View 4 Replies View RelatedProbably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.
View 4 Replies View RelatedIts probably very simple question, but i can not work it out.
I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.
Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).
How can i get these cells to read $0 or leave the cell empty?
The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.
Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.
Every time I think I've got this thing beat, they throw another curve at me!
If cell Q19 is blank, leave target cell blank
If cell Q19 is not blank, return the value of cell E$4
I have a worksheet (in a workbook with many worksheets) where some of the cells display the formula I put in after I leave the cell. I've made sure that the cell format is General before I type anything in the cell. I've inserted new rows, above where the problem is and have tried going to another cell in a different part of the work sheet but I have the same problem. I use the same formulas in two other worksheets. The formulas include other worksheets in them. example =IF(PlotMaster!B2="","",PlotMaster!B2). I can't figure out what is going on.
View 1 Replies View RelatedExcel Version: 2000
Workbook has the possiblities of 366 tabs at the bottom, but for a normal calendar year will only have 26.
The tab name is the ending date of the pay period without the year. So for Feb 25, 2007 the tab would be named 0225
On a yearly calculations page I am simply trying to say IF TAB 0225 exists then cell equals '0225'!A2 otherwise the cell should be blank.
I tried this, but gets a reference failure instead of putting nothing...
=IF('0102'!A2=0,"",'0102'!A2)
In this example the possible tab name would be 0102. If the tab exists, everything works fine, but since in this case it doesn't, I get a reference failure. #REF!
I NEED the result to be empty if the tab doesn't exist.
Any idea would be helpful. I'd rather not have 366 tabs when only 26 are needed for any calendar year.
cell A1 has the time (09:00), cell A2 has the minutes (60), cell A3 is the sum of A1+A2.
Im using this formula =A1+TIME(0,A2,0) - which is fine, except A1 is sometimes blank, so therefore I would like A3 to be blank.
I thought I could use this: =IF(A1,"","",(A1+TIME(0,A2,0)) But it doesn't work.
I am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
View 1 Replies View RelatedI'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I would like to use the IF function so when cell B2 is empty, cell C2 will stay empty. the formula =MONTH(B2&C2) need to be used when cell B2 is not empty. this to convert text to number in column C
This is the result that I would like to achieve
A
B
C
D
E
1
MONTH IN TEXT
MONTH IN NUMBER
2
MAY
5
3
4
JUNE
6
I have 2 similar question.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
A1 has a date of 03/10/07
B2 has formula =A1 + 7
I want to copy that formula down the column B2 but if
B3 is empty I get 01/07/1900
How Do I make so that if there's no date entered in
A column I get blank cells but still retain formulas,
I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.
The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.
After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.
How to leave a cell truly empty if the criteria of my IF statements is untrue. Currently, I'll write something like:
[Code] ....
But for some reason, when I copy and paste the resulting range of values elsewhere (to rid myself of the formula that determined them), the cells that did not return a value (where the statement is FALSE), are not recognized by a "Go To Special > Blanks" request, until I select all of the "empty cells" and clear them manually. Yet when I try to do a search on the same range for an empty space, I get no hits.
I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
View 7 Replies View Related