Posts by Pecoflyer

Which excel version are you using? Your profile is incomplete in that aspect

You're welcome

See attached for a possible solution

As your data only runs till 03/08 I used a fake date in T5 to illustrate

You should of course replace T5 in your formulas with TODAY()

Could you please explain the logic of your values in col G ? It's all very obscure to me. Thx

Another option

=INDEX(C1:C19,SUMPRODUCT(ROW(A1:A19)*--(A1:A19=K3)*--(B1:B19=K4)))

Please post a sample sheet

Hi

please post a sample with some representative data. Are your dates real dates ( right aligned ) or text looking like dates ( left aligned). (assuming no manual alignment)

If the latter select the date range Click Data - Text to columns - Finish. If right aligned you are OK.

Please post a sample sheet with some data and a manually built desired result. thx

Hi

please post a sample sheet. Thx

Hi

does your edition mean to have solved the problem ? If not please post a sample sheet. Thx

18 year old-thread...

Or try ="Q"&CEILING(a3,3)/3 ( where A3 holds the month)

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

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

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

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

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

Hello,

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

(replace with actual references where needed)

Have a look at https://docs.microsoft.com/en-…thmetic-inaccurate-result

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