# Posts by Pecoflyer

• ## Count formula based on a column heading

Hi and welcome

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.

You will also throw open the whole wonderful world of the powerful Pivot table functionality.

This would be MUCH easier using a standard table with three columns like Date - Venue - Initials

Starting from there usual COUNTIF formulas or a simple Pivot Table would do the job

• ## Accrual formula

On what basis does the accrual rate change between cell F37 and F38 ? ( goes from reference K2 to M2)

• ## Conditional formatting with 2 criteria of 2 columns.

You can use the following CF formula applied to the column you want to format ( I applied it to col B as an example)

=\$C3=AGGREGATE(14,6,(1/(\$B\$3:\$B\$59<=0.05))*\$C\$3:\$C\$59,1)specimendata1.zip

• ## Accrual formula

Please post a sample sheet, not a picture ( and add some mocked up results)

• ## Accrual formula

Posting a sample sheet with some data and manually expected results would help

• ## Accrual formula

Hello,

perhaps =MIN(PTObalance+80*0.1,140)

(replace with actual references where needed)

• ## function to find out format

Formatting has nothing to do with the underlying value. It is merely the way XL shows this value to the outside world ( be it on its own or through user manipulation).

By all means post a sample sheet with some data

• ## Referencing other worksheet’s data using a helper cell

Have a look at the INDIRECT function

• ## Based on value, find next highest value in range and return it's adjacent cell

There is probably an easier way

=INDEX(\$H\$8:\$H\$17,MATCH(INDEX(\$I\$8:\$I\$17,MATCH(TRUE,\$I\$8:\$I\$17>\$B\$5,0)),\$I\$8:\$I\$17,0))

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

• ## 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