Creating multi condition array formula? Lets try this again

ibesmond

New Member
Joined
Nov 26, 2010
Messages
17
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:
Row LabelsREIMR/TREIMR/TREIMR/T
6/1/201462.0070.660.0062.0070.66
6/2/2014123.30140.520.00123.30140.52
6/3/201462.0070.660.0062.0070.66
6/4/2014124.20141.540.00124.20141.54
6/5/2014155.53177.250.00155.53177.25
6/6/2014149.92170.850.00149.92170.85
6/7/2014145.07165.330.00145.07165.33
6/8/2014158.55180.690.00158.55180.69
6/9/2014162.01184.630.00162.01184.63
6/10/2014163.61186.450.00163.61186.45
6/11/2014164.83187.850.00164.83187.85
6/12/2014167.72191.130.00167.72191.13
6/13/2014160.91183.370.00160.91183.37
6/14/2014152.98174.340.00152.98174.34
6/15/2014124.74142.160.00124.74142.16
6/16/2014161.27183.790.00161.27183.79
6/17/2014134.82153.640.00134.82153.64
6/18/2014173.95198.240.00173.95198.24
6/19/2014170.78194.630.00170.78194.63
6/20/2014172.22196.270.00172.22196.27
6/21/2014167.34190.710.00167.34190.71
6/22/2014172.92197.070.00172.92197.07
6/23/2014144.17164.300.00144.17164.30
6/24/2014140.40160.010.00140.40160.01
6/25/2014178.01202.870.00178.01202.87
6/26/2014176.77201.450.00176.77201.45
6/27/2014177.38202.150.00177.38202.15
6/28/2014161.64184.210.00161.64184.21
6/29/2014156.79178.680.00156.79178.68
6/30/2014153.12174.500.00153.12174.50
Grand Total4,518.955,149.880.000.000.005,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)
FoliosCheck-in DateNights StayedTotal ChargesRoom/TaxMW
Jun Inv
MW
Jul Inv
Variance
503066/1/2014162.0070.6670.660.00
507216/1/2014162.0070.6670.660.00
632196/1/20143247.30281.84211.9869.86
634186/1/20143247.30281.84211.9869.86
712376/1/20145527.03600.63521.3779.26
906376/1/2014162.0070.6670.660.00
920056/1/2014162.0070.6670.660.00
922596/1/2014162.0070.6670.660.00
986326/1/2014162.0070.6670.660.00
995716/1/20143247.30281.84211.9869.86
503006/2/20142185.30211.18141.3269.86
901416/2/20143309.50352.72282.7070.02
985106/2/20147918.571,046.84914.50132.34
726096/3/20142186.20212.20212.040.16
960606/3/20144491.65560.30542.5017.80
966376/3/2014162.0070.6670.660.00
557796/4/20143429.65489.64471.8417.80
557806/4/20143429.65489.64471.8417.80
776436/4/20143429.65489.64471.8417.80
839876/4/20145733.27835.66773.1862.48
939366/4/20142279.73318.79309.399.40
511496/5/20143450.52513.43487.4126.02
780856/5/20146934.691,065.20983.6981.51
791406/5/20141155.53177.25168.019.24
794376/5/20142305.45348.10330.4617.64
980246/5/20141155.53177.25168.019.24
506216/6/20141149.92170.85162.458.40
514916/6/20142294.99336.18319.4016.78
515816/6/20141149.92170.85162.458.40
515826/6/20141149.92170.85162.458.40
828826/6/20142294.99336.18319.4016.78
874916/6/20141149.92170.85162.458.40
880856/6/20141149.92170.85162.458.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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Why an array formula as opposed to a just a lookup?

=lookup(1, condition1/condition2,returnRange)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top