# conditional calculations

1. ### Conditional correlation of returns

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...
2. ### EXCEL VBA or Formula: Split sets £ enteries based on criteria amount or close to it

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...
3. ### conditional range

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...
4. ### Complex Conditional Formatting Scenario

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...
5. ### Multi-result IF Statements...

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...
6. ### Complicated excel formula needed in date format

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...
7. ### Vinding name based on location and maximum value

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...
8. ### excluding condtinally formatted cells from calculation based on color and font with vba

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...
9. ### among three cells, if one is formatted (bolded), yield a value to a fourth cell

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...
10. ### Adding Several Logical Conditions

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...
11. ### Conditional formatting based on four date ranges

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...
12. ### Change tab color based on cell value

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...
13. ### Can I create a Conditional Formula based on the Color of a Cell?

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...
14. ### Importing Stock data into new cell, only on Positive Condition

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...
15. ### Conditional Calculate

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")
16. ### Multiple logic If function

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...
17. ### conditional cell manipulation with caculation in VBA

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...
18. ### Show Sum of group as a repeated line item!

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...
19. ### Prevent user from closing excel unless two cells are equal

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...
20. ### Conditional Formatting- Dates

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...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back