Are your percentages calculated from something? Can they possibly change?Hi,
I need a formula that will show me 77% for first 6 months followed by 60% for the remaining 6 months then 60% 3 months later so it shows as below.
View attachment 95456
Any help would be much appreciated.
23 07 18.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
E | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||||||
1 | ||||||||||||||||||||||||
2 | Month 1 | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | |||||||
3 | Aug-23 | 77% | 77% | 77% | 77% | 77% | 77% | 60% | 60% | 60% | 60% | 60% | 60% | 0% | 0% | 60% | 0% | |||||||
4 | Sep-23 | 0% | 77% | 77% | 77% | 77% | 77% | 77% | 60% | 60% | 60% | 60% | 60% | 60% | 0% | 0% | 60% | |||||||
swifttl |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K3:Z4 | K3 | =LOOKUP(IFERROR(DATEDIF($E3,K$2,"m")+1,0),{0,1,7,13,15,16},{0,0.77,0.6,0,0.6,0}) |
Hi,Welcome to the MrExcel board!
Does this do what you want?
23 07 18.xlsm
E J K L M N O P Q R S T U V W X Y Z 1 2 Month 1 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 Jul-24 Aug-24 Sep-24 Oct-24 Nov-24 3 Aug-23 77% 77% 77% 77% 77% 77% 60% 60% 60% 60% 60% 60% 0% 0% 60% 0% 4 Sep-23 0% 77% 77% 77% 77% 77% 77% 60% 60% 60% 60% 60% 60% 0% 0% 60% swifttl
Cell Formulas Range Formula K3:Z4 K3 =LOOKUP(IFERROR(DATEDIF($E3,K$2,"m")+1,0),{0,1,7,13,15,16},{0,0.77,0.6,0,0.6,0})
GetLastRow.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
33 | Months | Pct | |||
34 | -99 | 0 | 0 | ||
35 | 1 | 6 | 0.770 | ||
36 | 7 | 12 | 0.600 | ||
37 | 13 | 14 | 0.000 | ||
38 | 15 | 17 | 0.600 | ||
39 | 18 | 0.000 | |||
Sheet2 |
GetLastRow.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
41 | App Start | Aug-23 | Sep-23 | Oct-23 | Nov-23 | Dec-23 | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 | Jan-25 | Feb-25 | Mar-25 | |||
42 | 01-Aug-23 | 77% | 77% | 77% | 77% | 77% | 77% | 60% | 60% | 60% | 60% | 60% | 60% | 0% | 0% | 60% | 60% | 60% | 0% | 0% | 0% | |||
43 | 01-Sep-23 | 0% | 77% | 77% | 77% | 77% | 77% | 77% | 60% | 60% | 60% | 60% | 60% | 60% | 0% | 0% | 60% | 60% | 60% | 0% | 0% | |||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C42,C43:V43 | C42 | =VLOOKUP(monthdiff($A42,C$41),TableDatePct,3,TRUE) |
D42:V42 | D42 | =VLOOKUP(monthdiff($A42,D41),TableDatePct,3,TRUE) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
TableDatePct | =Sheet2!$A$34:$C$39 | C42:V43 |
VBA function
Function MonthDiff(dtRef As Date, dtStart As Date) As Integer
Dim dt1, dt2
dt1 = DateSerial(Year(dtRef), Month(dtRef), Day(dtRef))
dt2 = DateSerial(Year(dtStart), Month(dtStart), Day(dtStart))
MonthDiff = DateDiff("m", dt1, dt2) + 1
End Function