Search results

The formula solution that I provided is specifically for an #N/A error. But you are getting a #VALUE error? A #VALUE error suggests to me that one (or more) of your dates is keyed incorrectly and is being interpreted as a text value rather than a date. The solution for this is scrubbing your...

In the formula I provided: =IF(F2=MAX((I2=$I$2:$I$27)*($F$2:$F$27)),"last record","") replace this portion ($F$2:$F$27) with this: IFERROR($F$2:$F$27,0) New formula: =IF(F2=MAX((I2=$I$2:$I$27)*IFERROR($F$2:$F$27,0)),"last record","") See attached.

Sure. For a given row...we apply a True/False test: I2=I2:I27. It compares the ID in I2 with the IDs in the the whole column, and the resulting array is {TRUE;FALSE;TRUE;FALSE...}, returning True for each row that has a matching ID and False for all other rows. This array of TRUE and FALSE...

Nathan, If an array formula is satisfactory (remember to confirm with Control+Shift+Enter): =IF(F2=MAX((I2=$I$2:$I$27)*($F$2:$F$27)),"last record","") See attached.

If you are trying to count and multiply in the same cell, you can use a formula such as this: =COUNTIF(B2:B10,"Reason Name")*67 If your count is a field in a pivot table, you will want to use a formula something like this: =GETPIVOTDATA(.....)*67

Hunter, I'm not sure how well I have understood your request -- Assuming that you enter today's date in A1, paste this formula in I31 and see if it gives you the desired output: =IF(I$30<$A$1,I4,INDEX($C$4:$F$4,,MATCH(I$30,$C$3:$F$3,1))/12) See attached... Is this heading the right direction?

To answer your second question, this formula requires all 4 tests to be true in order to return a TRUE value. The 4 tests are: - Team Name match - Date match - Time is after start time - Time is before end time

The formula I provided is a special type of formula: an array formula. In order for it to work, there's a special trick -- a 'magic' keystroke, if you will. In order to 'fix' your sample file, all I have to do is click on cell E2, place my cursor in the formula at the top of the screen (as...

Screach, I'm glad to help, but as you'll see in the attached, I am unable to replicate the error that you are having on Sheet 1. I'm going to need more information to diagnose your problem.

Welcome to the forum! On the Data Tab, there is a command group called Outline. Click Ungroup; then, if a dialog box pops up, click Columns, OK.

Welcome to the forum. I had to do a little data scrubbing on your sample file because the times you have in Sheet 2 are reading as text "12:30pm" rather than times 12:30 PM. However, once that was resolved, I used an array formula to get the output you were seeking...

@John Jairo V Super! I knew I had over complicated D6.

First, I think you don't want Column C to return a month name only. You want it to return a real date, then you can change the formatting to display a month only. In Cell C6, enter and drag down to fill C7 and C8: =EOMONTH($D$1,ROWS($C$5:C5)-1) Then press Ctrl+1 (or right click and select...

If the boss expects you to develop reports that require all the data to be crunched to create a single output, I'd really encourage you to lean on the boss to put all the data into a single input. You can reassure him or her that even when it's all in one table, you can simply AutoFilter by...

Natalia, Welcome to the forum! If you are willing/able to keep all your invoices in a single table rather than on separate tabs, I think you'll be satisfied that a Pivot Table will do all that you've asked of the dashboard... Have you considered a Pivot Table for this application? (see attached)

In cell A5: =RANK.AVG(C5,OFFSET($C$4,MATCH(B5,$B$5:$B$34,0),,COUNTIF($B$5:$B$34,B5))) drag to fill Just for fun...I think this shorter array formula will work too?? =MATCH(C5,LARGE(N(($B$5:$B$34=B5)*($C$5:$C$34)),ROW($B$1:$B$10)),-1)

In order to focus the discussion, let's look at the error in cell G284. It is not created by the values in the yellow cells -- but by a value in the white cells! Your IFERROR formula in G271, G275, G279, G283 is returning an empty value: "" This is binding up the SUMPRODUCT, which needs a...

Try this formula in Cell H3, drag to fill. =IF($D3>H$1,$F3,$B3)&" "&H$2 (see attached).

Can you be more specific about what you are doing to create the error? If I enter a 0 value or delete the contents in the yellow cells, my orange cells still calculate without problems. I only generate a #VALUE error when I enter text into one of the yellow cells.

If I correctly understand, I think you can paste the following formula in E21 and copy in to the other cells in the grey section... =SUMPRODUCT(($C$3:$C$18=C21)*($E$3:$E$18),$E$5:$E$20) and modify slightly and paste the following into E41 with copy and paste in the blue section...

does this work? =IF(A2<=100,20,IF(A2<=350,25,IF(A2<=1000,55,"special pricing"))) I don't see the need for the AND()

Not sure I understand what specific information you need. Are you looking for a line of code to unhide the target column? Sheets(1).Columns(insert_column_number_here).Hidden = False

In cell E1: =INDIRECT("Worksheet!B"&((ROW()-1)*18)+2) Drag to fill.

Does it matter what order the duplicates appear in? If not, you can use the following array formula in cell E2 and drag down. =INDEX($A$2:$A$8,IF(1,N(LARGE((D2=$B$2:$B$8)*(ROW($A$2:$A$8)-1),COUNTIF($D$2:D2,D2))))) *remember, arrays must be confirmed with Ctrl+Shift+Enter (see attached)

I'd do it this way: =D5&E5&F5&G5&H5&I5 But you can also: =CONCATENATE(D5,E5,F5,G5,H5,I5) Unfortunately, you can't =CONCATENATE(D5:I5) without some add-ins.