# Posts by Pecoflyer

• ## Formula for: If a Cell contains text

In A21 on sheet 6 enter =IF(TYPE(Sheet1!F1)=2,Sheet1!F1,"")

This will only copy text as requested ( no errors, logical values or arrays)

• ## Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Are there other requirements you also need to add?

• ## Rotating Schedule

Hi

please post a sample sheet, it's easier to work with. Thx

• ## Overtime Claim Form

You are the "victim" of floating point arithmetic. If you format J8 as general you will see in the Format cells window that the value is very small but not null

Have a look at https://docs.microsoft.com/en-…thmetic-inaccurate-result on how to solve this problem

• ## Reference offset table range in formula

Be aware your formula will only be correct if there are no blank cells between data and it will prevent from using the A column for anything else

• ## Sumifs with true/false

How do you"select" the column? Please add some manually calculated results to show what you want to fo

• ## Sumifs with true/false

Try =SUMIF(\$H\$2:\$H\$4,"true",B\$2:B\$4) and drag right

• ## NumberFormat = "[h]:mm"

Try =A2/(B2*24) and pull down - Format the resulting cells as Number or General

Be aware that underlying values are NOT formatted, they are plain numbers. It is only what excel shows you that is formatted the way you want to see it, or the way XL thinks you want to see it

• ## Dropdown Menu & VLookup formula not working

Try using the FALSE switch in your VLOOKUP =VLOOKUP(A2&B2,Table1[#All],5,0) to get an exact match

You are using the TRUE switch (1)

• ## Vlookup formula does not work for some cell

For example D8 boils down to searching the "H2-2020" string which does not exist in the first column in your range \$A\$2:\$B\$61,2,0)

Same thing for the strings searched by the other N/A results

• ## calculate average of dates

Your result seems correct, just create a custom format like dd/mm instead of

*14/03/2001

perhaps post a sample sheet with some data and expected results? Pictures are nice to look at but useless

• ## Return result based on date range

As you can see the solution is exactly as my example

## Files

• Seasons.xlsx

• ## Return result based on date range

Attaching a small sample file would help

The basics would be

=INDEX(D1:D10,MATCH(1,(A1:A10="11/12")*(B1:B10<=YOUR_DATE)*(C1:C10>=YOUR_DATE),0))

To be committed as an array formula with Ctrl+Shift+Enter

• ## Select Cell Multiplier by Name

Could you please post a sheet instead of useless pictures, so we can work on it? Thanks

• ## excel VBA code not working

Perhaps explain what is not working? Post a sample sheet ( no pics please) ?