rwilliams09
New Member
- Joined
- Jun 18, 2017
- Messages
- 48
Hi all, I am trying to use name ranges so that the chart is filled ONLY by the YEARS in which there is data.
As you will see, for some reason, my chart goes beyond the years where there is data. The Financials (Presentation) tab only has the chart.
The Financials tab has the data itself. Columns AJ-AQ are used in the name ranges.
I am obviously doing something stupid... two questions:
- How do I fix my name ranges to only show data through the FINAL year? In this case, the final year is 2047?
-My name ranges for exmaple are... Years - =OFFSET(Financials!$AJ$4,0,,COUNTIF(Financials!$AJ$4:$AJ$59,"<>")) and Cash Flow is =OFFSET(Financials!$AJ$4,0,,COUNTIF(Financials!$AP$4:$AP$59,"<>"))..... AJ4 is 2010 in the table below...
- Why are my negative numbers showing up as positive in the chart?
AJ AK AL AM AN AO AP AQ
SO grateful for help..
As you will see, for some reason, my chart goes beyond the years where there is data. The Financials (Presentation) tab only has the chart.
The Financials tab has the data itself. Columns AJ-AQ are used in the name ranges.
I am obviously doing something stupid... two questions:
- How do I fix my name ranges to only show data through the FINAL year? In this case, the final year is 2047?
-My name ranges for exmaple are... Years - =OFFSET(Financials!$AJ$4,0,,COUNTIF(Financials!$AJ$4:$AJ$59,"<>")) and Cash Flow is =OFFSET(Financials!$AJ$4,0,,COUNTIF(Financials!$AP$4:$AP$59,"<>"))..... AJ4 is 2010 in the table below...
- Why are my negative numbers showing up as positive in the chart?
AJ AK AL AM AN AO AP AQ
| Cash Flow | US$MM | US$MM | US$MM | US$MM | US$MM | US$MM | US$MM |
| Revenue | Capex | Opex | Royalty and Other Taxes | Corporate Income Tax | Free Cash Flow | Abandonment | |
2010 | ($2,096.00) | ($264.75) | ($4.08) | ($2,364.84) | |||
2011 | $1,346.41 | ($3,022.88) | ($427.33) | ($269.80) | ($151.21) | ($2,524.81) | |
2012 | $3,324.31 | ($2,519.76) | ($506.16) | ($670.83) | ($563.11) | ($1,139.88) | |
2013 | $4,166.94 | ($2,900.70) | ($721.46) | ($848.09) | ($733.28) | ($1,401.81) | |
2014 | $5,826.20 | ($4,285.42) | ($1,072.72) | ($1,202.16) | ($903.59) | ($2,371.38) | |
2015 | $5,662.25 | ($7,560.10) | ($1,790.33) | ($1,171.77) | ($527.25) | ($6,072.62) | |
2016 | $8,057.08 | ($7,595.37) | ($2,833.79) | ($1,676.90) | ($571.99) | ($5,493.61) | |
2017 | $14,006.10 | ($4,024.28) | ($3,672.09) | ($2,853.81) | ($1,283.96) | $40.29 | |
2018 | $22,567.01 | ($1,574.66) | ($4,216.41) | ($4,608.80) | ($2,547.74) | $5,291.24 | |
2019 | $21,769.40 | ($2,226.08) | ($4,519.47) | ($4,436.48) | ($2,281.16) | $4,394.52 | |
2020 | $21,757.74 | ($3,608.78) | ($4,569.54) | ($4,434.76) | ($2,195.12) | $3,169.53 | |
2021 | $22,727.41 | ($3,846.70) | ($4,612.03) | ($4,631.80) | ($2,272.66) | $3,439.08 | |
2022 | $23,587.25 | ($2,550.81) | ($4,651.40) | ($4,806.59) | ($2,351.89) | $5,155.90 | |
2023 | $23,892.57 | ($2,426.38) | ($4,926.80) | ($4,868.69) | ($2,302.60) | $5,386.64 | |
2024 | $24,253.78 | ($2,304.01) | ($4,942.31) | ($4,942.05) | ($2,314.68) | $5,751.01 | |
2025 | $24,645.56 | ($2,348.00) | ($4,959.86) | ($5,021.66) | ($2,324.23) | $5,977.70 | |
2026 | $23,459.00 | ($487.79) | ($4,896.46) | ($4,779.80) | ($2,185.11) | $7,370.76 | |
2027 | $21,152.20 | ($439.81) | ($4,775.65) | ($4,309.76) | ($1,907.80) | $6,513.56 | |
2028 | $18,996.05 | ($394.97) | ($4,662.89) | ($3,870.44) | ($1,647.21) | $5,711.49 | |
2029 | $17,636.62 | ($1,161.39) | ($4,591.41) | ($3,593.42) | ($1,461.47) | $4,467.75 | |
2030 | $17,002.76 | ($1,164.06) | ($4,557.52) | ($3,464.22) | ($1,363.08) | $4,276.98 | |
2031 | $15,840.26 | ($329.27) | ($4,496.49) | ($3,227.33) | ($1,224.25) | $4,646.59 | |
2032 | $14,249.25 | ($296.20) | ($4,008.48) | ($2,903.17) | ($1,121.95) | $4,217.28 | |
2033 | $12,796.21 | ($265.99) | ($3,527.75) | ($2,607.12) | ($1,036.54) | $3,842.35 | |
2034 | $11,456.81 | ($238.15) | ($3,457.99) | ($2,334.24) | ($872.16) | $3,331.85 | |
2035 | $10,243.62 | ($212.93) | ($2,943.41) | ($2,087.06) | ($826.73) | $3,071.50 | |
2036 | $9,160.06 | ($190.41) | ($2,435.56) | ($1,866.29) | ($798.79) | $2,864.69 | |
2037 | $8,192.55 | ($170.30) | ($1,933.79) | ($1,669.17) | ($787.31) | $2,707.42 | |
2038 | $7,328.27 | ($152.33) | ($1,888.74) | ($1,493.08) | ($679.33) | $2,378.56 | |
2039 | $6,556.13 | ($136.28) | ($1,848.54) | ($1,335.76) | ($580.86) | $2,082.35 | |
2040 | $5,866.10 | ($121.94) | ($1,812.62) | ($1,195.18) | ($490.48) | $1,814.79 | |
2041 | $5,249.19 | ($109.12) | ($1,780.47) | ($1,069.49) | ($406.82) | $1,572.27 | |
2042 | $4,697.61 | ($97.65) | ($1,751.73) | ($957.11) | ($327.44) | $1,349.69 | |
2043 | $4,204.39 | ($87.40) | ($1,726.02) | ($856.61) | ($250.83) | $1,144.76 | |
2044 | $3,763.38 | ($78.23) | ($1,703.06) | ($766.76) | ($175.60) | $958.31 | |
2045 | $3,368.91 | ($70.03) | ($1,682.50) | ($686.40) | ($96.29) | $797.48 | |
2047 | ($2,896.55) | ($2,896.55) |
SO grateful for help..
