Return a * if any cells in a range are101

Microsoft Excel 2003 (full product)
November 2, 2010 at 14:06:09
Specs: Windows XP
Sorry, the question heading left off a < symbol! It should read that I want to return an asterisk on one sheet if any cells in a range on another sheet are <101.

I maintain a fleet of four aircraft, and one of the tools I use is an Excel workbook that has two sheets, one that tracks the total time on each aircraft in hours (Daily Status), and the other that tracks an annual budget for parts due on each aircraft during the current fiscal year (FY 10-11). FY 10-11 tracks each aircraft's parts and the times those parts need to be replaced.

This workbook was written, and is used in Excel 2003, SP3.

What I want to do is put an indication by a particular aircraft on the Daily Status sheet (such as an asterisk) if any of that particular aircraft’s parts on the FY 10-11 sheet are due within 100 hours.

For example, Aircraft #1 currently has 3,400.0 hours on it according to the Daily Status sheet, and it has an engine due to be replaced at 3,500.0 hours, as listed on the FY 10-11 sheet. A calculation is done on FY 10-11 to show that the part is due in 100 hours. I want an asterisk to show up by Aircraft #1 on the Daily Status sheet informing me to check the FY 10-11 sheet to see what parts are coming due.

It is easy to get an asterisk from only one cell on the FY 10-11 sheet by using this:
=IF('FY 10-11'!E14<101,"*"," "), where E14 is the time remaining for that part.

My problem is that I have a list of several parts for each aircraft, any of which may be within 100 hours. Say for example the range for the times the parts are due for aircraft #1 is E14:E20, I don't know how to make one cell on the Daily Status sheet return an asterisk if any (or all) of the cells in E15:E20 are <101 hours.

Can this be done?


See More: Return a * if any cells in a range are101

Report •


#1
November 2, 2010 at 15:19:48
You could try a =COUNTIF()
Something like:

=IF(COUNTIF('FY 10-11'!E14:E20,"<101"),"*","")

MIKE

http://www.skeptic.com/


Report •

#2
November 2, 2010 at 15:47:47
Mike, that's an interesting function.

It seems that the IF is considering any value other than a 0 to be TRUE.

I would have suggested:

=IF(COUNTIF(E14:E20,"<101")>0, "*", "") but it appears that you don't need the ">0"

However, even when your IF returns the "*", this will return FALSE:

=TRUE = COUNTIF(E14:E20,"<101")

To make that statement TRUE, you need the ">0"

=TRUE = (COUNTIF(E14:E20,"<101") >0)

Interesting.


Report •

#3
November 2, 2010 at 16:08:37
Mike, this was right on the money! Awesome, thanks so much! I am not familiar with the COUNTIF function at all, but whatever it is doing, it works for what I need. Looks like I have some reading to do!

Report •

Related Solutions

#4
November 2, 2010 at 17:08:19
Actually, I think it is more in the line of:
=COUNTIF() will return TRUE if it returns a value within your specifications.

So if the formula =COUNTIF('FY 10-11'!E14:E20,"<101")
finds a value that is less then 101 from the range E14:E20,
it returns the count of that value, and is true.
If it does not find a match, it returns NULL, and so is false.

MIKE

http://www.skeptic.com/


Report •

#5
November 3, 2010 at 11:12:04
re: "So if the formula =COUNTIF('FY 10-11'!E14:E20,"<101") finds a value that is less then 101 from the range E14:E20, it returns the count of that value, and is true."

However, if that were that case, then wouldn't you think that this would evaluate to TRUE under the same conditions? It doesn't.

=TRUE = COUNTIF('FY 10-11'!E14:E20,"<101")

In fact, playing around a bit, I found that this evaluates to TRUE:

=IF(3, TRUE, FALSE)

Why Excel considers the number 3 (or the result of a COUNTIF) to be TRUE when used inside the IF function is something that I don't quite understand.


Report •

#6
November 3, 2010 at 12:13:22
I'm not sure how your using the TRUE() function.
In my help files (2007) it says that the TRUE() function

Returns the logical value TRUE

and

This function takes no arguments.

In fact, playing around a bit, I found that this evaluates to TRUE:

=IF(3, TRUE, FALSE)

What you might find even more interesting is this:

=IF(1,TRUE,FALSE) returns TRUE
=IF(0,TRUE,FALSE) returns FALSE

Any number greater than zero evaluates to TRUE,
while zero evaluates to FALSE.

Ergo, if =COUNTIF() returns any value >0 it's TRUE.

MIKE

http://www.skeptic.com/


Report •

#7
November 3, 2010 at 20:00:50
re: What you might find even more interesting is this:

=IF(1,TRUE,FALSE) returns TRUE
=IF(0,TRUE,FALSE) returns FALSE

No, I don't find that interesting at all. That's exactly what I would expect since Excel stores the Boolean TRUE and FALSE as 1 and 0.

However, 3 is not 1, therefore I don't quite know why Excel considers 3 (or any other number other than 0, including negative numbers) to be equivalent to TRUE - within an IF.

In an exchange with someone who knows Excel much more deeply than I can ever hope to, I was told:

" If Excel expects a logical (result) value, and is presented with a number, it will (as programmed to be very kind to you) usually 'evaluate' that number as a logical value.

Zero being taken as False, and any other value being taken as Not-'False', which is 'True' "


Report •

Ask Question