#1
|
|||
|
|||
Date specific formula Query
Hi
I hope someone can help. Per the attached screen shot I am trying to implement date specific formulas in columns W through AK. Column O is the total funding available. A 20% retention (Column R) and 5% audit check amount (Column Q) are withheld from this total until the end of the contract period leaving column P as the amount to work with. The monthly amount (Column U) is calculated by dividing Column P by Column T. The first payment is dependant on whether column V matches the cells W1 through AK1 and is 2 months up front. The following months will be the monthly amount in column U. When the sum of these payments matches the amount of Column P then the Retention amount is released. Lastly the audit amount it released. The sum of all these amounts should total the amount in Column O. The formulas I have tried to use are as follows: W2 (This is an easy calculation and works fine.) =IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(W$ 1),MONTH(W$1),DAY(0)),$U2*2,"") X2 =IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(x$ 1),MONTH(x$1),DAY(0)),U2*2,IF(SUM($W2)=$P2,$R2,IF( SUM($W2)=SUM($R2+$P2),$Q2,IF(SUM($W2)=$O2,"",$U2)) )) Y2 =IF(DATE(YEAR($V2),MONTH($V2),DAY(0))=DATE(YEAR(y$ 1),MONTH(y$1),DAY(0)),U2*2,IF(SUM($W2:$Y2)=$P2,$R2 ,IF(SUM($W2:$Y2)=SUM($R2+$P2),$Q2,IF(SUM($W2:$y2)= $O2,"",$U2)))) etc. (Note: The attachment is how the spreadsheet should look but I have been unable to match this.) Thanks in advance |
#2
|
||||
|
||||
Hi and welcome
Please post a sample sheet. Pics are nice to look at but hard to work with
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
First of all, may I point out that your Column P should be "Less 25%", not "Less 15%"
Maybe something like this (see attached)... Basically, the equation for the first month is a nested if: =IF(I1>$H2,$G2,IF(I1=$H2,2*$G2,"")) If test month (I1) > Process Month (H2), 1x the Monthly Amount (G2), else If test month (I1) = Process Month (H2), 2x the Monthly Amount (G2), else [Less than]: "" The other months all have the same nested ifs: =IF(SUM($I2:I2)=$A2,"",IF(SUM($I2:I2)=$B2+$D2,$C2, IF(SUM($I2:I2)=$B2,$D2,IF(J1>$H2,$G2,IF(J1=$H2,2*$ G2,""))))) If the amounts to the left = Total Allocation (A2), then "", else If the amounts to the left = Less 25% (B2) + Retention (D2), then Audit (C2), else If the amounts to the left = Less 25% (B2), Retention (D2), else The first equation above. |
#4
|
|||
|
|||
Thanks for the update. Ill give that a go and update my findings.
|
#5
|
|||
|
|||
Perfect, thanks Gebobs
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
MS Query date format | remigio | Word | 3 | 08-21-2012 10:54 PM |
Tracking changes: Reviewing Pane: Insertions etc: Is it possible to get specific date | semiotically | Word | 1 | 07-29-2012 03:29 PM |
How to count year lapse (rounded off) based on specific date | KIM SOLIS | Excel | 1 | 11-01-2011 10:50 AM |
Need a date formula | MPAVLAS | Excel | 3 | 08-12-2010 10:04 PM |
Date format always interpreted as a formula | Butch Jackman | Excel | 1 | 02-14-2006 11:27 AM |