Search results

{=SMALL($A$1:$A$8*ROW($A$1:$A$8),COUNTIF($A$1:$A$8,"=FALSE")+2)} or {=LARGE($A$1:$A$8*ROW($A$1:$A$8),COUNTIF($A$1:$A$8,"=TRUE")-1)} perhaps? or as an alternative to COUNTIF() {=LARGE($A$1:$A$8*ROW($A$1:$A$8),SUM(--$A$1:$A$8)-1)}

Are you trying to hard-code the 20:00 value in the formula? Perhaps you could put 20:00 in another cell (C1, for example), and then write a formula like so: =IF(A1>$C$1,"no","yes") attachment

Everybody: I'm trying to execute an Index(,Match) operation -- the lookup_array is 100,000 rows of data...sorted ascending. But my match function seems to fail after testing row 87,500...So if the lookup_value happens to be on the 87,501st row (or later), the match function automatically...

Well, I found a solution that is imperfect but adequate: =$A$2+(ROW()-2)/48 It's volatile, so I'll have to Copy/Paste Special Values Only when I'm done; and it still has fractional discrepancies, but I can drag it 106,000 rows without cumulative loss of time. (I just have to remember that my...

Friends, I need to tabulate a large volume of data -- which is essentially a set of utility meter readings for every half hour increment over a 4 year period. As I set up the sheets, I want to auto fill the left column with a date/time stamp for each half hour increment, like so: 1/1/10 0:00...

Does this do it? (See attached.) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim datarng As Range Set datarng = Sheets("Hoja1").Range("C5:BT28") If Not Intersect(Target, datarng) Is Nothing Then With Sheets("Hoja1").Range("C4:BT4") .Font.Bold = False...

The "C" & i portion of your code is passing a Cell Reference rather than a Cell Value through to the Match function... Try replacing with Range("C" & i).Value Like so: SubjectRow = Application.WorksheetFunction.Match(Range("C" & i).Value, Sheets("Equity").Range("B:B"), 0) I would probably...

I suggest creating a CF rule such as =$F3>100 See attached. All best!

When I use the formula you provided: =SUM(10:11) Excel calculates the sum of the values in all the cells of Row 10 and all the cells of Row 11. Which is functionally synonymous with =SUM(A10:XFD11) ***However, this is a theoretical formula; I think Excel will automatically simplify this to...

Tigger, I don't want to mess too much with your file, so I've put a new formula in only the first few rows of Column A and B; you'll want to drag it down to fill if it's what you want... Column A has an array formula (confirmed with Ctrl+Shift+Enter): =IFERROR(INDEX('Hazard Identification...

Nothing special...I just changed the borders and font color to gray. :) Glad that it's going to work for you. All best.

Test this one... =IF(YEAR(TODAY())<2016,0,IF(YEAR(VLOOKUP($C4,G:H,2,0))<2016,IF(DAY(TODAY())<VLOOKUP($C4,G:H,2,0)-EOMONTH(VLOOKUP($C4,G:H,2,0),-1),MONTH(TODAY())-1,MONTH(TODAY())),0))

Like so?...(attached) There's room for 100 entries in your source data column and up to 50 different types of fruit...

Your original post indicates that the Agent earns 1 credit per month -- and the test date provided in your sample file is 11/24/2014 (which was 14 months ago...) I assumed that there should be 14 available credits. Have I misunderstood your intent?

Will this work? =IF(OR(AND($C2="Unapproved",$D2=100),AND($C2="Unapproved",$B2=TODAY()),AND($C2="Approved",$D2<100)),TRUE,FALSE) See attached for reference. If you just want TRUE/FALSE as your outputs, you actually don't need the IF at all...this will return the same results...

See attached as a starting point -- is this where you want to be?

JE, Your Original Formula: =IF(YEAR(TODAY())<2016,0,IF(YEAR(VLOOKUP($C4,G:H,2,0))<2016,MONTH(TODAY()),MONTH(TODAY())-MONTH(VLOOKUP($C4,G:H,2,0)))) This formula is returning MONTH(TODAY()) -- which happens to be the numeral that represents today's month (January). That's why your formula is...

When you create a propose a multiplier (as you suggested in your first post) -- you aren't dealing with real money any more -- you are purposely inflating and deflating local currency values to normalize your comparisons... It's almost like your states are using different currencies -- one...

Santhos, We recently had a very similar need in our office, and I found another solution (following on John Jairo's): =SUM(INDEX(A4:M4,,N(IF(1,IFERROR(SMALL(IF(B4:M4>0,COLUMN(B4:M4),""),{1,2,3}),0))))) Still an array formula, but considerably more concise than my first suggestion. Sorry so...

I plugged in your formula (as far as I understand it) and mine, and I found that the resulting ranks were the same both ways. But I'm not happy with either, because neither takes into account the distribution of whole set....that is to say, the rank of the whole set is skewed by a few outlying...

Welcome to the forum, Dan! Remember in high-school physics class when the teacher curved everyone's grade based on the achievement of the highest student? I didn't ever like that very much...(this always gets complaints from the median students) If you know each person's average profit per...

By "tables" do you mean you have data in an Excel Table, or a simple range? I've tried to re-create both scenarios in the attached file...can you review and clarify where you are having a problem?

lomer, Welcome to the forums! I appreciate that you sent me a message about this post because I hadn't been watching this thread. See attached, with the revisions you requested in your post -- as best as I have understood them. Note that the table on the right side of the dashboard provides...

Deepak -- you pointed me in the right direction: Thanks! I was able to customize my ribbon without adding or changing the language settings: Giving credit where credit is due, with your direction, I found this site that answered my question almost exactly...

Uh-oh. I was afraid I'd have to do something like that...Maybe a linked table in Word... Meantime -- still open to suggestions.