Thank you for the response - I finally had some time to take a shot at this.

The goal is for the formula to SUM outstanding risk by date. In this case, I want to know my outstanding risk on 8/11/16. I am asking it to look at the table on the 'Trade Log' sheet, and SUM the RISK if 8/11/16 is equal to or in between the OPEN and CLOSE DATES.

The first screenshot is the 'Capital Usage' page where I am inputting the formula. The second shot is where I want to reference - the 'Trade Log' sheet, where individual trades are entered.

I am receiving some !VALUE error messages as follows.

This is where I am inputting the formula:



This is a shot of the 'Trade Sheet' data I am trying to reference:

I think you have the formula backwards.

The Criteria_range1 is the list of all of the trade open dates. Think of it as the "lookup" list - you are looking at all of these dates to find ones that match what you're looking for.

The Criteria1 is the value that you are comparing the list against. You are comparing the list against a specific date - starting at B4. You want any open date that is less than or equal to B4. This will give you all of the trades that were opened on or before B4

The Criteria_range2 needs to be the list of close dates. It's your second "lookup list".

The Criteria2 is the same as Criteria1 except that you're now looking for close dates that are greater than B4. This will give you any trades that are closed after B4

When these two are combined, you will get a list of trades that were opened on or before the specific date AND that were closed after the specific date.

(edit) I think you may want to remove the equals sign from the second criteria. If you include it the formula will include the dates on which you closed the trade. It's up to you to determine if you want those calculated in your risk assessment for the day.

THANK YOU!!!! Finally got this working. Much appreciated.

Frustrated, because I have a similar formula in another workbook that works fine, but I'm trying to build a model for determining my sales manager's variable compensation based on reaching certain quota thresholds.

I have =IF(B8<E6,0, IF(B8>E6, (B8-E6)/C10*E21, IF(B8>E7, 25000 + (B8-E7)/C10*E22)))

And it's not adding the $25,000

I added more tiers/thresholds and built this mess of a formula, but it works fine:

=IF(B9<F6,B9/C11*E21, IF(B9<F7, 11550+(B9-F6)/C11*E22, IF(B9<F8, 34650+(B9-F7)/C11*E23, IF(B9>=F8, 70350+(B9-F8)/C11*E24))))

Anyone seeing something I'm not?

Never mind, figured it out

Grunching - if I have a table with a long list of transactions sorted by client, and I want to have Excel keep the column formatting and headers but throw every client's transactions into a separate tab, what's the fastest / best way to do that?

Sent from my SM-N910P using Tapatalk

Depends on the number of clients. I'd guess if less than 20 it's probably quicker to replicate it a bunch of times on new tabs and then filter/delete all other clients in each one or set up separate pivot tables that filter for one client each

If more than 20 or you want a repeatable process, write a macro. I can't think of any built in functionality that would do that

This is pretty easy to do using Array formulas.

In a nutshell, (assuming your clients are listed in column A), you would use IF(A:A=name of client you want on its own sheet,ROW(A:A),"") to return the list of row numbers containing your client. You can pull this list into column A of another spreadsheet, use LARGE(A:A,ROW(A1)) copied down to remove the blanks, then you would use INDEX referencing your source data in the other columns to return the corresponding data from these rows.

If this makes absolutely no sense to you, spend a bit of time reading up on array formulas, they're really quite cool.

Making a spreadsheet to track recipes. I have a ton of tabs, each for a different recipe (calories and amounts calculated, etc). I have a single sheet at the beginning which is like a "homepage" that explains the color coding of the tabs (each protein a different color, etc). Is there a way to make shortcuts on this homepage to each recipe? Desired effect is I click a cell and it's like some sort of GUI/button that takes me to the page for that recipe, without me having to scroll through a ton of tabs.

You can do that with a hyperlink - it doesn't have to be a URL it can be internal and take you to any cell.

Perfect - thanks

I think he meant at once with VBA or something creating the tabs for him. Anyway, I imagined his initial column containing client names more than once in the A column for each transaction. Your version would only return the initial row number, right? How would you combat that?

is there anyway to link 2 or more cells so that whatever is entered into one cell it will populate the other cell(s) with the same information?

Do you need it to be shown as text, i.e. why won't setting one cell equal to the other work?

the input can be text or alphanumeric.

One cell equals the other won't work because that only works 1 way.

I am trying to be able to have cells A:1=B:1=C:1 and the goal is to be able to enter the input into any of the three cells. Setting then all equal 1-creates circularity and 2 only works the first time you enter any input. If that is ever changed the cells don't update.

I'm really trying to think this through but your avatar is not helping. Why do you need this? It's generally messy to have cells that can either be input or output.

Anybody have any idea how to convert the following into google spreadsheets? They seem to not have an equivalent option for "Holidays" or at least not one I can find.

=WORKDAY(IF(E4="EOM",EOMONTH($F$1,0),DATEVALUE(CON CATENATE(MONTH($F$1),"/",DAY($E4),"/",YEAR($F$1)))),-F4,Holidays)

Holidays is probably just be a named range in your excel spreadsheet. Google Sheets supports the same thing. The parameters to WORKDAY are the same in Excel and Google Sheets.

I see what I did wrong. Didn't pull over an additional sheet. Thanks man.

Nope, nevermind. I still get an error that says Unknown range name: holidays

Did you define the range? In Data->Named Ranges in Google Sheets, add the ranges that you see in Formulas->Name Manager in Excel

I have a table with stock market data that I update several times per day. There are six columns that have conditional formatting, turning the cells either green or red, based on the data values. Each row is a separate stock.

I need to send out a daily report, showing which stocks are "green" in 6/6 columns, 5/6 columns, 4/6 columns, 3/6 columns, and so-on, down to 0/6 columns.

Currently, I am filtering by color on each column - green on six columns = Group #1, Green on 5/6 columns, = Group #2. Removing filters as I go, to create the lists.

I know there must be a way to automate this process. The only way I can think is to have a series of duplicate tables on separate sheets, each with the filters preset, then have those tables update with the master table.

Any better ideas?

Depending on what the criteria are, actual implementation could vary, but one way would be to create 7 new columns at the end of your table that count the stocks that meet the criteria (one column is 6/6, one is 5/6, etc; each stock gets a unique number starting at 1 if it meets that columns criteria). Then a separate table that index/matches the unique numbers to report all the stocks

Yeah, create a separate table and have formulas that input a one in a cell if the criteria is met and a 0 otherwise. You can then just sum the columns to get the number of criteria hit. Elsewhere you can easily add an output that lists everything that has a 6 total, 5 total etc.

I use an Excel spreadsheet for my time clock. Column A to Column D are the relevant pieces of data. In order, it goes: Date, Start Time, End Time, Total Hours. I'm looking to find the average number of hours I work each day. The issue is I don't total my hours per day in the sheet, e.g., there will be multiple 3/21/17 entries in Column A.

With a single formula, can I find out the average total for each entry? I know I can just create a new data set where I SUMIF each unique date, but I want learn if it can be done in the current set.

How do I do a COUNTIF for unique values or values that appear 1 or more times or something to segment out the number of dates?

Edit: Okay, figured it out. I can add up unique values in a range by combining SUMPRODUCT and FREQUENCY formulas. Had no clue about FREQUENCY, so that's nice.

=(O1/(SUMPRODUCT(--(FREQUENCY(Hours!A:A,Hours!A:A)>0))-1)) counts the unique dates.