For those of you that looked at my old post, I am sorry. I tried to post with a screen shot and it didn't work. I tried to modify and delete without success.
Here is my question.
Hello,
I posted a tread a while back and received great feedback.
I'm working to automate the following:
<tbody>
</tbody>
Column A = Pivot Table [Date]
Column B = Array Formula {=MEDIAN(IF(Table3467[DATE]=A4,Table3467[REIM]))}
Column C = Formula =B4+ROUND(B4*0.0925,2)+ROUND(B4*0.04712,2)
Column E = Formula =F4/(1+(ROUND(1*0.0925,2)+ROUND(1*0.04712,2)))
Column F = (Need formula for these cells)
Column H = Formula =B4-E4
Column I = Formula =C4-F4
Basically for Column F = I am looking for a formula to use to do this...
I have a data set (Not a table = and I do not want to change it into a table)
<tbody>
</tbody>
I want the formula to look at the date located on Column A (The first table). E.g. 6/1/14. The a want to look at the data. (the second table)
If the date from the first table equals the date on the second table and the nights stayed equals 1, then I want the array formula to return the value in either column labeled [MW Jun Inv] or [MW Aug Inv] column from the second table. I want this to be an array formula that can be copied down the column for each day of the month.
Might be a difficult formula to write, but I am out of ideas on how to do it. Especially if I don't change the second table into an actual table like the formula from Column B.
If you have any ideas, it would be greatly appreciated.
Thank you
Here is my question.
Hello,
I posted a tread a while back and received great feedback.
I'm working to automate the following:
Row Labels | REIM | R/T | REIM | R/T | REIM | R/T | ||
6/1/2014 | 62.00 | 70.66 | 0.00 | 62.00 | 70.66 | |||
6/2/2014 | 123.30 | 140.52 | 0.00 | 123.30 | 140.52 | |||
6/3/2014 | 62.00 | 70.66 | 0.00 | 62.00 | 70.66 | |||
6/4/2014 | 124.20 | 141.54 | 0.00 | 124.20 | 141.54 | |||
6/5/2014 | 155.53 | 177.25 | 0.00 | 155.53 | 177.25 | |||
6/6/2014 | 149.92 | 170.85 | 0.00 | 149.92 | 170.85 | |||
6/7/2014 | 145.07 | 165.33 | 0.00 | 145.07 | 165.33 | |||
6/8/2014 | 158.55 | 180.69 | 0.00 | 158.55 | 180.69 | |||
6/9/2014 | 162.01 | 184.63 | 0.00 | 162.01 | 184.63 | |||
6/10/2014 | 163.61 | 186.45 | 0.00 | 163.61 | 186.45 | |||
6/11/2014 | 164.83 | 187.85 | 0.00 | 164.83 | 187.85 | |||
6/12/2014 | 167.72 | 191.13 | 0.00 | 167.72 | 191.13 | |||
6/13/2014 | 160.91 | 183.37 | 0.00 | 160.91 | 183.37 | |||
6/14/2014 | 152.98 | 174.34 | 0.00 | 152.98 | 174.34 | |||
6/15/2014 | 124.74 | 142.16 | 0.00 | 124.74 | 142.16 | |||
6/16/2014 | 161.27 | 183.79 | 0.00 | 161.27 | 183.79 | |||
6/17/2014 | 134.82 | 153.64 | 0.00 | 134.82 | 153.64 | |||
6/18/2014 | 173.95 | 198.24 | 0.00 | 173.95 | 198.24 | |||
6/19/2014 | 170.78 | 194.63 | 0.00 | 170.78 | 194.63 | |||
6/20/2014 | 172.22 | 196.27 | 0.00 | 172.22 | 196.27 | |||
6/21/2014 | 167.34 | 190.71 | 0.00 | 167.34 | 190.71 | |||
6/22/2014 | 172.92 | 197.07 | 0.00 | 172.92 | 197.07 | |||
6/23/2014 | 144.17 | 164.30 | 0.00 | 144.17 | 164.30 | |||
6/24/2014 | 140.40 | 160.01 | 0.00 | 140.40 | 160.01 | |||
6/25/2014 | 178.01 | 202.87 | 0.00 | 178.01 | 202.87 | |||
6/26/2014 | 176.77 | 201.45 | 0.00 | 176.77 | 201.45 | |||
6/27/2014 | 177.38 | 202.15 | 0.00 | 177.38 | 202.15 | |||
6/28/2014 | 161.64 | 184.21 | 0.00 | 161.64 | 184.21 | |||
6/29/2014 | 156.79 | 178.68 | 0.00 | 156.79 | 178.68 | |||
6/30/2014 | 153.12 | 174.50 | 0.00 | 153.12 | 174.50 | |||
Grand Total | 4,518.95 | 5,149.88 | 0.00 | 0.00 | 0.00 | 5,149.88 |
<tbody>
</tbody>
Column A = Pivot Table [Date]
Column B = Array Formula {=MEDIAN(IF(Table3467[DATE]=A4,Table3467[REIM]))}
Column C = Formula =B4+ROUND(B4*0.0925,2)+ROUND(B4*0.04712,2)
Column E = Formula =F4/(1+(ROUND(1*0.0925,2)+ROUND(1*0.04712,2)))
Column F = (Need formula for these cells)
Column H = Formula =B4-E4
Column I = Formula =C4-F4
Basically for Column F = I am looking for a formula to use to do this...
I have a data set (Not a table = and I do not want to change it into a table)
Folios | Check-in Date | Nights Stayed | Total Charges | Room/Tax | MW Jun Inv | MW Jul Inv | Variance |
50306 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
50721 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
63219 | 6/1/2014 | 3 | 247.30 | 281.84 | 211.98 | 69.86 | |
63418 | 6/1/2014 | 3 | 247.30 | 281.84 | 211.98 | 69.86 | |
71237 | 6/1/2014 | 5 | 527.03 | 600.63 | 521.37 | 79.26 | |
90637 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
92005 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
92259 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
98632 | 6/1/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
99571 | 6/1/2014 | 3 | 247.30 | 281.84 | 211.98 | 69.86 | |
50300 | 6/2/2014 | 2 | 185.30 | 211.18 | 141.32 | 69.86 | |
90141 | 6/2/2014 | 3 | 309.50 | 352.72 | 282.70 | 70.02 | |
98510 | 6/2/2014 | 7 | 918.57 | 1,046.84 | 914.50 | 132.34 | |
72609 | 6/3/2014 | 2 | 186.20 | 212.20 | 212.04 | 0.16 | |
96060 | 6/3/2014 | 4 | 491.65 | 560.30 | 542.50 | 17.80 | |
96637 | 6/3/2014 | 1 | 62.00 | 70.66 | 70.66 | 0.00 | |
55779 | 6/4/2014 | 3 | 429.65 | 489.64 | 471.84 | 17.80 | |
55780 | 6/4/2014 | 3 | 429.65 | 489.64 | 471.84 | 17.80 | |
77643 | 6/4/2014 | 3 | 429.65 | 489.64 | 471.84 | 17.80 | |
83987 | 6/4/2014 | 5 | 733.27 | 835.66 | 773.18 | 62.48 | |
93936 | 6/4/2014 | 2 | 279.73 | 318.79 | 309.39 | 9.40 | |
51149 | 6/5/2014 | 3 | 450.52 | 513.43 | 487.41 | 26.02 | |
78085 | 6/5/2014 | 6 | 934.69 | 1,065.20 | 983.69 | 81.51 | |
79140 | 6/5/2014 | 1 | 155.53 | 177.25 | 168.01 | 9.24 | |
79437 | 6/5/2014 | 2 | 305.45 | 348.10 | 330.46 | 17.64 | |
98024 | 6/5/2014 | 1 | 155.53 | 177.25 | 168.01 | 9.24 | |
50621 | 6/6/2014 | 1 | 149.92 | 170.85 | 162.45 | 8.40 | |
51491 | 6/6/2014 | 2 | 294.99 | 336.18 | 319.40 | 16.78 | |
51581 | 6/6/2014 | 1 | 149.92 | 170.85 | 162.45 | 8.40 | |
51582 | 6/6/2014 | 1 | 149.92 | 170.85 | 162.45 | 8.40 | |
82882 | 6/6/2014 | 2 | 294.99 | 336.18 | 319.40 | 16.78 | |
87491 | 6/6/2014 | 1 | 149.92 | 170.85 | 162.45 | 8.40 | |
88085 | 6/6/2014 | 1 | 149.92 | 170.85 | 162.45 | 8.40 |
<tbody>
</tbody>
I want the formula to look at the date located on Column A (The first table). E.g. 6/1/14. The a want to look at the data. (the second table)
If the date from the first table equals the date on the second table and the nights stayed equals 1, then I want the array formula to return the value in either column labeled [MW Jun Inv] or [MW Aug Inv] column from the second table. I want this to be an array formula that can be copied down the column for each day of the month.
Might be a difficult formula to write, but I am out of ideas on how to do it. Especially if I don't change the second table into an actual table like the formula from Column B.
If you have any ideas, it would be greatly appreciated.
Thank you