For a group of agencies that have applied for grant funding, I'm trying to create a spreadsheet that will calculate the amount to be awarded according their score on the grant application. I want the agencies that receive 100% score to receive full funding and the remaining agencies to receive the remaining amount in a weighted fashion according to their scores. I'm having a hard time creating anything that will self-adjust without getting a circular reference. I've attached a spreadsheet with the sample data.
I want to format 3 columns in excel depending on certain value from another column. By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.
I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.
At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.
I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.
The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
How to conditionally format an array formula? The basic formula is an index(match criteria1 and match criteria2. I have come across sites where they mention you can incorporate aggregate into the formula to remove the Alt+Ctrl+Shift thus allowing for the formula to work in conditional formatting. Shown below is the formula I am trying to use. Basically if the formula below = today() then be yellow.
I am using a conditional formula to compare if a cell in column A is higher than a cell in column B. If a cell is blank the cell is formatting. I am trying to highlight only cells with numbers. Any formula to place in the conditional format formula that will evaluate the cells excluding a blank cell?
ie: grapes and pears should be highlighted because value in column B is less than column A but the conditional format is highlighting apples also where apples should have no highlighting because the value in column B is none or blank.
A B oranges 2.49 3.25 grapes 1.99 1.89 apples 1.00 pears 1.11 1.03
This should be pretty straight forward but the solution has escaped me so far. I have some formulas that ratably spread monthly budgets across the life of a program. Ocasionally these budget formulas are hard keyed over with a value which overrides the ratable budget formula. I would like to conditionally format all cells that contain hard keyed values so they don't get inadvertently copied to new budget lines.