PDA

View Full Version : Formula help



sjvenz
09-06-2008, 11:32 PM
I'm trying to write a formula that will use the table 1 below and apply a formula that will filter and add detail to it the end result is shown below.

The formula below is just one part
=IF(L2="",IF(LEFT(K2,5)+$D$16>$B$1,"EX ","AFD"),IF(ISERROR(SEARCH("AFD",LEFT(L2,FIND(".",L2)-1))),IF(L2="BLP","BLP",LEFT(L2,FIND(".",L2)-1)),IF(K2="BLP",LEFT(L2,FIND(".",L2)-1),IF(K2="","AFD",IF(LEFT(K2,5)+$D$16>$B$1,"EX "&"AFD","AFD")))))



1
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP |13:00 1300OR.2 |14:00 AFD.14 | 17:00 1700OR | 17:00 AFD.11 | 17:00 AFD.11
3 | 11:00 1100SUP | BLP | BLP | 00:00 0000OR.4 | 03:00 AFD.2 | 05:00 AFD
4 05:00 0500OR.2 | 08:00 0800OR.4 | 08:00 0800OR.4 | BLP | BLP | 15:00 1500OR.2 | 15:00 1500OR.1
5 15:00 1500OR.1 | 16:00 1600OR.3 | 23:00 2300OR.2 | | 04:00 0400OR.4 | BLP | BLP


formula reulst
a | b | c | d | e | f | g
1 Mon | Tue | Wed | Thu | Fri | Sat | Sun
2 BLP | BLP | 13:00 1300OR | AFD | ex 17:00 1700OR | ex AFD | ex AFD
3 ex |11:00 1100SUP | BLP | BLP | 00:00 0000OR | AFD | AFD
4 05:00 0500OR | 08:00 0800OR | 08:00 0800OR | BLP | BLP |15:00 1500OR| ex 15:00 1500OR
5 ex 15:00 1500OR | ex 16:00 1600OR | ex 23:00 2300OR | ex |04:00 0400OR| BLP | BLP

Bascially what it needs to be able to do is check if the cell contains BLP then BLP is still returned. but if it contains 08:00 0800OR then it calculates this and if it goes over a 24hr period then and EX is entered before the value in the next cell. Like at f4 to g4 then g4 to a5

mdmackillop
09-07-2008, 12:38 AM
Can you post a sample workbook with your attempts so far and desired results. I'm not sure if I'm replicating the layout correctly.

Bob Phillips
09-07-2008, 02:37 AM
How does f4 and g4 go over a 24 hour period, they are both 15:00, and why does A3 get an ex?

sjvenz
09-07-2008, 02:20 PM
The rows can vary from 32 to over 300, it depends on the download. The number of rows is calculated and the total placed into cell a3, this is then used to run the formula.


As for calculating ex if the day before is 15:00 then in the formula it looks at this and cell a1 and adds the both together and if it goes over 24:00 then an ex is placed in the adjacent cell. This then also must apply from Sun to Mon.

I've attached a spreadsheet where I've tried some test.

Bob Phillips
09-07-2008, 03:01 PM
Which of those results is closest, and where does it not succeed?

sjvenz
09-07-2008, 10:11 PM
Formula test 2 comes close

Bob Phillips
09-08-2008, 12:27 AM
... and where does it not succeed?