I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
View 2 Replies
I have two columns of data, Tables 1 and 2 in the attached file, and I would like to have in Table 3 a list containing each item (AAAA, BBBB etc) in columns A and B.
I could paste the data from columns A and C into column E and then sort in alphabetical order, before deleting the duplicates, but there must be a cleverer way of achieving this result. It would be great if the solution was in some way dynamic because I need to do this more than once for ranges of different sizes.
When I use a reference to populate a cell in anothe worksheet and I use the delete key to remove the entry, a zero shows up in the cell of the worksheet wit the cell reference. For example if I use =AM!A3 and use the delete key to delete the entry in cell A3 of worksheet AM, the worksheet with the reference =AM!A3 shows a zero. Is there some way to prevent the zero from appearing in the cell with the reference?
View 3 Replies View RelatedI have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
A student is in their exam and they have entered the formula correctly but the result is not showing - only showing 0.
Formula shows in formula bar.
Formula view is turned OFF.
Turning it on shows the formulka
Why cant she see the results?
I created a formula in a cell and it worked fine but then I created the same formula in the cell just below it and I changed some of the numbers and when I hit the enter button the formula showed up in the cell and not the final result which would be the answer to the formula. Does anyone know why it would do this? I have an example below....
View 6 Replies View RelatedI want cell B4 on sheet 3, to show the value of cell B4 on sheet one ... only if there is a value in B4 on sheet one then i want the rest of row 4 to be copied to sheet 3 also.
HOWEVER if the value in cell B4 sheet 1 is BLANK/EMPTY then i want the full row to be over looked and the next row to be checked (B5 all the way to B30)
Once B4-30 on Sheet 1 has been checked and only the rows showing a data/value have been copied to sheet 3, i then want the same process to start again on sheet 2 - Again only the rows showing data/values to be copied.
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View 9 Replies View RelatedI have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.
The same formula could be another place in my worksheet and work fine.
I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.
I tried doing Ctrl + ~, but it did not work
View 14 Replies View RelatedI have a formula when C15 is empty it shows -18 value. I need if the value is blank then show blank?
Code:
=IF((Data!C15-32)*5/9="","",(Data!C15-32)*5/9)
I was given this Formula to use in a cell to calculate the total hours from 2 different cells, from inputting date and time from and to dates..as an example from 4/1/14 0:00 thru 4/1/14 12:00.....The problem I am encountering is that when the spreadsheet is 1st opened "+1" is visible in the cell where this formula is. I am asking how the formula can be altered to have the "+1" not visible in the cell before any date and time is entered.
= INT(F13-D13)*24+(((F13-D13)-INT(F13-D13))/0.04166666)+1
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedMy formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View RelatedIs there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedIn a particular CSV Excel file I want to populate "Description" column with a HTML template Code by merging 3 columns of HTML. The HTML has almost 20,000 characters. Now before merging and after merging when I copy paste the HTML in any cell it doesn't show all the code that I have copied. and when I merge all the 3 columns. a lot of HTML data seems to be missing. I think excel has some sort of limit and it is not allowing all these 20,000 characters merged and combined in 1 cell. I am stuck at this point and can't upload the CSV as the HTML is missing a lot of coding.
View 2 Replies View RelatedI would like to be able to enter the data into the grey cells and then have the yellow cells auto generate a result. The only problem is that the required value in cell B5 is from when the entered value in B4 is looked up in the table, rounded UP to the next highest value and then the answer from the next cell.
So, if I enter in B4 a value of 500, a lookup is made in the table and the next highest value would be 553 with the correct answer shown in B5 being 13.
My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.
For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.
I have created a drop-down menu combobox of districts and depending on your choice of districts the list of Health Centers in each district changes. What I want to do is to protect the contents of the cells that contain the health centers. The problem is that when I lock the cells and I choose a district, the Health Center cells cannot change because the cells are locked. Is there anyway around it?
I have attached the sample (Please look at sheet 2. The column that I want to block is the column FOSA)
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
I have a problem to solve
I have 2 worksheets named 'Products' and 'Order'
At the 'Order' i use 3 cells
Code
Pallets
Cases
When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.
What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.
1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000
2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000
because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50
I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.
Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.
I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.
the criteria would be
value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0
I can get a formula to work for a single instance but not multiple conditions.
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls
I have the following table in excel:
Price: Percentage:
$150 10%
$100 12%
$50 14%
I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
Is there a way to incorporate these 3 boxes into one? So if I enter a figure into any cell, it will calulate it for the other two? Without overwriting the existing formula. I have attached the problem.
problem.xlsx
