conditional calculations

Hello forum, Thank you in advance for your help. I need to create a correlation matrix (simple, edited pic attached) that returns the correlation coefficient of columns 1 and 2 for the subset of periods where a third column, Equity, is down at least -2%. Within a conditional correlation, I...

Hello all, I'm not sure is this is possible, everything I have looked at seems like it needs to be exact amount. I'm hoping someone can help. Please see image attached. In column B is the list of amounts I want to seperate into C D E F G columns. The total of £189,733.00 is divide by K6...

I need to create a range between B5:M5 that is defined by the location of P1 (phase 1) and P2 (phase 2) in Row 4. The range should begin one column to the right and one row down from P1 and end at the cell one column to the left and one row down from P2. P1 and P2 are both movable and so...

Hi Everyone, Thank you in advance for whatever help you may be able to provide. Purpose/Scope Analyzing the effectiveness of retail store renovations and relocations. Existing Material I have many slices or views, but let's assume there is only one. The back-end looks like this. Please...

I am running a contest at work and I need a cell to return different values IF based on the returns results. For example the column header is "Current Reward" and I want it to return either "$500", "$750" or "$1,000" depending on the the data at specific value benchmanrks. I know I am doing...

Please help with me with calculating the formula for the due date column: I need to calculate the due date for 3 different types reports with varying timelines and dependencies. Each due dates must fall on business days but is calculated based on calendar days from pre-defined start dates (if...

I got stuck on the formula to find the value of a cell, based on two conditions. column A has location, column J has number of days. I now want to find the name with the highest number of days for each different location from column S.(rows in between do hold data, but are not relevant for thuis...

I have created the following function to calculate the sumproduct of two ranges of cells, but only for cell pairs which do not share a cell shade or font color which matches that of another specified cell. It works fine for cells which I have manually formatted the cell shade and font color...

I have a spreadsheet in which I have three cells, one of which is bolded, and I need the fourth column to yield a specific value based on which of the three cells is bolded. More specifically, if the first of the three cells is bolded, I'd like the fourth cell to yield a value of '1'. If the...

Hello Team, I need to be able to add several IF statement logical conditions: If C4<=20% then is 20 If D4<=1.07% then is 20 If E4>=75% then is 20 I need to be able to add the "20" for each value if is true, in the sample all are true, the answer should be 60, if only two are true then the...

Hi, I'm trying to add a "traffic light" conditional format based on four separate date cells for a plant hire schedule. These four cells are Date On, Date Off, Date Suspended From, and Date Suspended To. What I want to achieve is: - A Green light if today's date is greater than or equal to the...

Afternoon all, I've read a bunch of different posts regarding VBA code to change the color of a tab based on a condition but haven't been able to get any of them to work. I have about 100 different worksheets/tabs that are similiar, and I want to change each individual tab color based on the...

I am I'm trying to create a formula that changes the color of Cell F4 based on the color of the cell in F4; If F4 cell colour is green or gold and value is of T4 is Y then colour T4 green. This means: If Cell F4 = [the color green] && T4=='Y' then Cell T4 also gets the color green i.e Cell F4...

Column has dates in first cell, only when conditions in other cells are met. I want to produce a condensed Column of this data, in a new Column that eliminates all rows of data without the dates, with no blank spaces, meaning that the cells where the conditions were not met are eliminated from...

I realize you cannot do a simple IF statement like in regular Excel. Goal: A field in a dimension has a 'Y' or 'N'. If it says 'Y' then: 100 If it says 'N' then: CALCULATE(DIVIDE([Job Line Costs To Date],SUM('JOB COST DETAILS'[Job Line Estimate Cost Amount])),Job[Job Comp Y or N]="N")

Hi, I was hoping someone can help me with an issues I'm having in excel. I'm pretty sure this will be an easy fix for most of the users on here. I'm still trying to get my head around if statements and finding it quite hard to achieve the end result. I have the following table (example). I want...

I would like to use VBA to automate something I do a lot, but my knowledge of VBA is not much, so I would appreciate some help. What I want to do: Search column 8 for 0's (only one digit in the cell and it is a zero, not looking for zeros as in 10, 101, 0.01 etc.), and if found then check...

Here is my data: Code NA1 NA2 Share NA3 Totals A x x 1 x 16 A x x 10 x 16 B x x 5 x 8 A x x 5 x 16 C x x 7 x 7 B...

Hi, I am creating a timesheet for users to enter their daily working hours as well as an allocation of their total hours worked. The allocation is very important as they work in different projects. Is there a way I can prevent the workbook from closing and an error message appearing to let...

This is for invoicing for a business. I have a list of dates for when project invoices get sent out to clients, and I would like to keep track of how long they are out before payment is received from the client. I want the number to be accurate to today's date, but when payment is received...