Make Cell Blank For Exceptions (calculating Blood Sugars)
Aug 14, 2007
I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:
Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.
Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.
The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.
View 9 Replies
ADVERTISEMENT
Jul 22, 2008
I am using a “borrowed” formula from a coworker that I do not really understand however it is working for me, sort of.
=+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V17,$W17,$X17,$Y17,$Z17,$AB17,$AC17,$AD17,$AO17,$AZ17)))),I F(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="FFFFFFFFFFFFF FFFFF","FAIL","PASS"),"FAIL *")
What this does is read the range of cells and immediately fails a quality check if it is in a critical cell, or fails if multiple non critical items fail.
I want to use an if/then to make cell blank if there is no data in one particular cell (which means no quality inspection performed). Here is what I tried:
IF(V18="","",+IF(ISERROR(FIND("F",UPPER(CONCATENATE($V18,$W18,$X18,$Y18,$Z18,$AB18,$AC18,$AD18,$AO18 ,$AZ18)))),IF(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($R$2),"NA",""),"FAIL",""),"PASS",""),4)="F FFFFFFFFFFFFFFFFF","FAIL","PASS"),"FAIL *"))
The leaving the cell blank part of the formula works however it allows the remainder of the formula to only work if “fail” appears in the first cell it reads (in this case V18). If the “fail” appears in any of the other critical cells, it does not work at all.
Also as a side project not sure what the substituting to $R$2 does so if anyone knows how to clean this up, let me know.
View 14 Replies
View Related
Oct 29, 2009
I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.
This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")
A B C
10.1810.82
View 14 Replies
View Related
Jan 15, 2009
I have 2 worksheets, i have one sheet that has a form with lists, when nothing is selected the cells are blank. On the other sheet I have a few cells which = some of the sheets on the other worksheet. when nothing is selected, the cell displays 0 even though the cell its supposed to be displaying is blank.
View 4 Replies
View Related
Mar 20, 2009
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.
View 3 Replies
View Related
Feb 12, 2010
How do I put in iserror in to this to make the cell go blank with N/A is returned. Only ever done this on vlookups. =IF(I15<E8,"TOOLING REQUIRES CALIBRATION","")
View 2 Replies
View Related
Aug 15, 2009
How can I make a cell display 0 or remain blank instead of #N/A?
I have several cells that are either VLOOKUP or just normal sum functions but they all display #N/A.
View 9 Replies
View Related
Mar 6, 2014
I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?
View 9 Replies
View Related
Apr 10, 2014
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
[Code] ......
View 2 Replies
View Related
Oct 23, 2009
First, I can get the balance formula, and when I drag the square at the bottom right, I am able to drag the entire formula down...I want to do that. However, when I drag it down, the number keeps popping up. I was wondering if there is a way to be able to drag the entire thing down and be able to make it so that it is hidden (the cell is blank) unless the debit or credit cell in the corresponding row is filled. Also, if possible, I want to be able to drag it down to infinity, so it can go on forever, so that there is no need to always drag it down when I add more things in it. I am attaching what I have till now.
View 5 Replies
View Related
Jul 20, 2013
section 100
section 200
100-1
200-1
I have a large spreadsheet with text, numbers and blank cells. Is there a code a can use to make all blank cell have a dash (-) without affecting the other cell with data.
View 8 Replies
View Related
Apr 17, 2008
Does anyone know how to use a macro to make a cell background blank if it is not orange or dark blue?
View 12 Replies
View Related
Oct 30, 2013
I want to make a cell blank if nothing is written in another cell. I have used this formula in C4: =IF(ISNUMBER(C3),IF(C3<180,C3+180,C3-180),"")
View 1 Replies
View Related
Sep 26, 2008
Make a cell blank if another cell is blank? This is my formula, it checks two dates. It is in cell M5.
View 4 Replies
View Related
Apr 11, 2014
I want to solve the problem of giving an alert for opening a new reagent vial for blood sugar testing
View 4 Replies
View Related
Apr 22, 2004
Looking for a formula to average rows of blood pressure readings. Column H contains readings as general format, e.g. 120/80 Have tried numerous formulas using LEFT and RIGHT to isolate #'s to sum/average with no luck at all. Compounding factor is that both #'s can be either 2 or 3 digits.
View 8 Replies
View Related
Feb 17, 2014
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
View 2 Replies
View Related
Nov 17, 2008
#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
=RtHistory("IDN",B5,B7:D7,B6,"LAY:HOR NULL:SKIP ZERO:SKIP SORT:ASC")
the #N/A errors are based on the line above. Is there a way to remove the #N/A? I want to make Row B/C/D above blank
View 10 Replies
View Related
May 16, 2014
I have column g as a dollar amount, and a running total in column h, where g4+h3=h4. I want the cells in h to appear blank until there is a dollar amount entered into the corresponding g cell.
View 10 Replies
View Related
Dec 30, 2009
I get reports weekly that contain too much information. Using "IF - LIKE - THEN" in my macro I copy and paste only the relevent data from the new work sheet to a separate worksheet.
It works fine except, it pastes over the previous data instead of below it overwriting the old info. Please have a look at my code, How do I instruct it to paste in the first empty row?
View 4 Replies
View Related
Mar 20, 2009
I want to make all the cells that are outputted as blank by my formula a certain color to distinguish them from non blank cells. my current formula: [B1=IF(A1=0,"",A1)]. my guess would be to use an [AND] function but I do not know how to command a cell to change color without using the toolbar.
View 4 Replies
View Related
Feb 28, 2014
To establish the order for on duty personnel every month, I need to find a proper formula.
I have attached the workbook.
So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.
In range H:T, I have a table with the personnel names (in the header) and the individual holidays.
What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.
Attached File : ON DUTY.xlsm‎
View 5 Replies
View Related
Apr 7, 2009
I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.
Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.
View 7 Replies
View Related
Jul 23, 2009
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
View 4 Replies
View Related
Jan 20, 2010
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
View 3 Replies
View Related
Dec 15, 2009
I have a workbook (Data) that I am entering "job title" into column G. Based on key words in the job title I am then manually entering "level" into Column V.
I have created a worksheet (Level Matrix) that has the following
Column A = Job title
Column B - Level
Column C = Exceptions
What I have in column A are the key words - as an example *Director* (wildcard Director Wildcard); in column B is the word Director
What I want the system to do is look for the word DIRECTOR anyplace in the job title entered into Column G of the data worksheet (hence why I have the title between wildcards. IF it finds it then I want to add the level automatically (from Column B of the Level Matrix worksheet).
However if the compare finds any of the words in the title that match any of the words in the Exceptions Column (Column C of the Level Matrix) then I DO NOT WANT To autoload the Director level- it would load DNA. As an example- an Art Director would not load "Director" becasue the word "art" is one of the words in the exceptions column of the Level Matrix.
View 9 Replies
View Related
Aug 22, 2007
I need to create a custom sort which will cause entries "TBD" and blank to appear at the bottom of my spreadsheet. Any other value will be sorted alphabetically. I'm not sure how to create my sort list to do this. I tried "*, TBD " but this did not work.
View 2 Replies
View Related
Feb 12, 2014
I have a workbook with timesheet records for a list of resources from multiple locations. These resources have logged time against the projects which is captured on a monthly basis. These time records are against holidays declared by the company. I would like to see if there are any of them who have logged time incorrectly on a holiday.?
View 4 Replies
View Related
Aug 11, 2009
When using conditional formatting, I have it set to the following:
C2=60 (Reference Cell)
C3=5 (Reference Cell)
Conditional Format Settings
If C3 >= C2/12 then pattern set to Green
If C3 < C2/12 then pattern set to Red
Here is the problem - when you have a value in C3, everything is fine, it's either green or red. If C3 is blank, it defaults to green because the conditional format is true.
Is there anyway to add something in there to have no color when C3 is not populated?
View 11 Replies
View Related
Oct 2, 2008
List File Name with multiple exceptions. I have this
View 2 Replies
View Related
