Creating multi condition array formula? Lets try this again

I

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:
[TABLE="width: 680"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]REIM[/TD]
[TD]R/T[/TD]
[TD][/TD]
[TD]REIM[/TD]
[TD]R/T[/TD]
[TD][/TD]
[TD]REIM[/TD]
[TD]R/T[/TD]
[/TR]
[TR]
[TD]6/1/2014[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[/TR]
[TR]
[TD]6/2/2014[/TD]
[TD]123.30[/TD]
[TD]140.52[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]123.30[/TD]
[TD]140.52[/TD]
[/TR]
[TR]
[TD]6/3/2014[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[/TR]
[TR]
[TD]6/4/2014[/TD]
[TD]124.20[/TD]
[TD]141.54[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]124.20[/TD]
[TD]141.54[/TD]
[/TR]
[TR]
[TD]6/5/2014[/TD]
[TD]155.53[/TD]
[TD]177.25[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]155.53[/TD]
[TD]177.25[/TD]
[/TR]
[TR]
[TD]6/6/2014[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[/TR]
[TR]
[TD]6/7/2014[/TD]
[TD]145.07[/TD]
[TD]165.33[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]145.07[/TD]
[TD]165.33[/TD]
[/TR]
[TR]
[TD]6/8/2014[/TD]
[TD]158.55[/TD]
[TD]180.69[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]158.55[/TD]
[TD]180.69[/TD]
[/TR]
[TR]
[TD]6/9/2014[/TD]
[TD]162.01[/TD]
[TD]184.63[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]162.01[/TD]
[TD]184.63[/TD]
[/TR]
[TR]
[TD]6/10/2014[/TD]
[TD]163.61[/TD]
[TD]186.45[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]163.61[/TD]
[TD]186.45[/TD]
[/TR]
[TR]
[TD]6/11/2014[/TD]
[TD]164.83[/TD]
[TD]187.85[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]164.83[/TD]
[TD]187.85[/TD]
[/TR]
[TR]
[TD]6/12/2014[/TD]
[TD]167.72[/TD]
[TD]191.13[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]167.72[/TD]
[TD]191.13[/TD]
[/TR]
[TR]
[TD]6/13/2014[/TD]
[TD]160.91[/TD]
[TD]183.37[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]160.91[/TD]
[TD]183.37[/TD]
[/TR]
[TR]
[TD]6/14/2014[/TD]
[TD]152.98[/TD]
[TD]174.34[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]152.98[/TD]
[TD]174.34[/TD]
[/TR]
[TR]
[TD]6/15/2014[/TD]
[TD]124.74[/TD]
[TD]142.16[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]124.74[/TD]
[TD]142.16[/TD]
[/TR]
[TR]
[TD]6/16/2014[/TD]
[TD]161.27[/TD]
[TD]183.79[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]161.27[/TD]
[TD]183.79[/TD]
[/TR]
[TR]
[TD]6/17/2014[/TD]
[TD]134.82[/TD]
[TD]153.64[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]134.82[/TD]
[TD]153.64[/TD]
[/TR]
[TR]
[TD]6/18/2014[/TD]
[TD]173.95[/TD]
[TD]198.24[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]173.95[/TD]
[TD]198.24[/TD]
[/TR]
[TR]
[TD]6/19/2014[/TD]
[TD]170.78[/TD]
[TD]194.63[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]170.78[/TD]
[TD]194.63[/TD]
[/TR]
[TR]
[TD]6/20/2014[/TD]
[TD]172.22[/TD]
[TD]196.27[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]172.22[/TD]
[TD]196.27[/TD]
[/TR]
[TR]
[TD]6/21/2014[/TD]
[TD]167.34[/TD]
[TD]190.71[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]167.34[/TD]
[TD]190.71[/TD]
[/TR]
[TR]
[TD]6/22/2014[/TD]
[TD]172.92[/TD]
[TD]197.07[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]172.92[/TD]
[TD]197.07[/TD]
[/TR]
[TR]
[TD]6/23/2014[/TD]
[TD]144.17[/TD]
[TD]164.30[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]144.17[/TD]
[TD]164.30[/TD]
[/TR]
[TR]
[TD]6/24/2014[/TD]
[TD]140.40[/TD]
[TD]160.01[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]140.40[/TD]
[TD]160.01[/TD]
[/TR]
[TR]
[TD]6/25/2014[/TD]
[TD]178.01[/TD]
[TD]202.87[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]178.01[/TD]
[TD]202.87[/TD]
[/TR]
[TR]
[TD]6/26/2014[/TD]
[TD]176.77[/TD]
[TD]201.45[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]176.77[/TD]
[TD]201.45[/TD]
[/TR]
[TR]
[TD]6/27/2014[/TD]
[TD]177.38[/TD]
[TD]202.15[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]177.38[/TD]
[TD]202.15[/TD]
[/TR]
[TR]
[TD]6/28/2014[/TD]
[TD]161.64[/TD]
[TD]184.21[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]161.64[/TD]
[TD]184.21[/TD]
[/TR]
[TR]
[TD]6/29/2014[/TD]
[TD]156.79[/TD]
[TD]178.68[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]156.79[/TD]
[TD]178.68[/TD]
[/TR]
[TR]
[TD]6/30/2014[/TD]
[TD]153.12[/TD]
[TD]174.50[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]153.12[/TD]
[TD]174.50[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]4,518.95[/TD]
[TD]5,149.88[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD]5,149.88[/TD]
[/TR]
</tbody>[/TABLE]


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)
[TABLE="width: 655"]
<tbody>[TR]
[TD]Folios[/TD]
[TD]Check-in Date[/TD]
[TD]Nights Stayed[/TD]
[TD]Total Charges[/TD]
[TD]Room/Tax[/TD]
[TD]MW
Jun Inv[/TD]
[TD]MW
Jul Inv[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]50306[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]50721[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]63219[/TD]
[TD]6/1/2014[/TD]
[TD]3[/TD]
[TD]247.30[/TD]
[TD]281.84[/TD]
[TD]211.98[/TD]
[TD][/TD]
[TD]69.86[/TD]
[/TR]
[TR]
[TD]63418[/TD]
[TD]6/1/2014[/TD]
[TD]3[/TD]
[TD]247.30[/TD]
[TD]281.84[/TD]
[TD]211.98[/TD]
[TD][/TD]
[TD]69.86[/TD]
[/TR]
[TR]
[TD]71237[/TD]
[TD]6/1/2014[/TD]
[TD]5[/TD]
[TD]527.03[/TD]
[TD]600.63[/TD]
[TD]521.37[/TD]
[TD][/TD]
[TD]79.26[/TD]
[/TR]
[TR]
[TD]90637[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]92005[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]92259[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]98632[/TD]
[TD]6/1/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]99571[/TD]
[TD]6/1/2014[/TD]
[TD]3[/TD]
[TD]247.30[/TD]
[TD]281.84[/TD]
[TD]211.98[/TD]
[TD][/TD]
[TD]69.86[/TD]
[/TR]
[TR]
[TD]50300[/TD]
[TD]6/2/2014[/TD]
[TD]2[/TD]
[TD]185.30[/TD]
[TD]211.18[/TD]
[TD]141.32[/TD]
[TD][/TD]
[TD]69.86[/TD]
[/TR]
[TR]
[TD]90141[/TD]
[TD]6/2/2014[/TD]
[TD]3[/TD]
[TD]309.50[/TD]
[TD]352.72[/TD]
[TD]282.70[/TD]
[TD][/TD]
[TD]70.02[/TD]
[/TR]
[TR]
[TD]98510[/TD]
[TD]6/2/2014[/TD]
[TD]7[/TD]
[TD]918.57[/TD]
[TD]1,046.84[/TD]
[TD]914.50[/TD]
[TD][/TD]
[TD]132.34[/TD]
[/TR]
[TR]
[TD]72609[/TD]
[TD]6/3/2014[/TD]
[TD]2[/TD]
[TD]186.20[/TD]
[TD]212.20[/TD]
[TD]212.04[/TD]
[TD][/TD]
[TD]0.16[/TD]
[/TR]
[TR]
[TD]96060[/TD]
[TD]6/3/2014[/TD]
[TD]4[/TD]
[TD]491.65[/TD]
[TD]560.30[/TD]
[TD]542.50[/TD]
[TD][/TD]
[TD]17.80[/TD]
[/TR]
[TR]
[TD]96637[/TD]
[TD]6/3/2014[/TD]
[TD]1[/TD]
[TD]62.00[/TD]
[TD]70.66[/TD]
[TD]70.66[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]55779[/TD]
[TD]6/4/2014[/TD]
[TD]3[/TD]
[TD]429.65[/TD]
[TD]489.64[/TD]
[TD]471.84[/TD]
[TD][/TD]
[TD]17.80[/TD]
[/TR]
[TR]
[TD]55780[/TD]
[TD]6/4/2014[/TD]
[TD]3[/TD]
[TD]429.65[/TD]
[TD]489.64[/TD]
[TD]471.84[/TD]
[TD][/TD]
[TD]17.80[/TD]
[/TR]
[TR]
[TD]77643[/TD]
[TD]6/4/2014[/TD]
[TD]3[/TD]
[TD]429.65[/TD]
[TD]489.64[/TD]
[TD]471.84[/TD]
[TD][/TD]
[TD]17.80[/TD]
[/TR]
[TR]
[TD]83987[/TD]
[TD]6/4/2014[/TD]
[TD]5[/TD]
[TD]733.27[/TD]
[TD]835.66[/TD]
[TD]773.18[/TD]
[TD][/TD]
[TD]62.48[/TD]
[/TR]
[TR]
[TD]93936[/TD]
[TD]6/4/2014[/TD]
[TD]2[/TD]
[TD]279.73[/TD]
[TD]318.79[/TD]
[TD]309.39[/TD]
[TD][/TD]
[TD]9.40[/TD]
[/TR]
[TR]
[TD]51149[/TD]
[TD]6/5/2014[/TD]
[TD]3[/TD]
[TD]450.52[/TD]
[TD]513.43[/TD]
[TD]487.41[/TD]
[TD][/TD]
[TD]26.02[/TD]
[/TR]
[TR]
[TD]78085[/TD]
[TD]6/5/2014[/TD]
[TD]6[/TD]
[TD]934.69[/TD]
[TD]1,065.20[/TD]
[TD]983.69[/TD]
[TD][/TD]
[TD]81.51[/TD]
[/TR]
[TR]
[TD]79140[/TD]
[TD]6/5/2014[/TD]
[TD]1[/TD]
[TD]155.53[/TD]
[TD]177.25[/TD]
[TD]168.01[/TD]
[TD][/TD]
[TD]9.24[/TD]
[/TR]
[TR]
[TD]79437[/TD]
[TD]6/5/2014[/TD]
[TD]2[/TD]
[TD]305.45[/TD]
[TD]348.10[/TD]
[TD]330.46[/TD]
[TD][/TD]
[TD]17.64[/TD]
[/TR]
[TR]
[TD]98024[/TD]
[TD]6/5/2014[/TD]
[TD]1[/TD]
[TD]155.53[/TD]
[TD]177.25[/TD]
[TD]168.01[/TD]
[TD][/TD]
[TD]9.24[/TD]
[/TR]
[TR]
[TD]50621[/TD]
[TD]6/6/2014[/TD]
[TD]1[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD]162.45[/TD]
[TD][/TD]
[TD]8.40[/TD]
[/TR]
[TR]
[TD]51491[/TD]
[TD]6/6/2014[/TD]
[TD]2[/TD]
[TD]294.99[/TD]
[TD]336.18[/TD]
[TD]319.40[/TD]
[TD][/TD]
[TD]16.78[/TD]
[/TR]
[TR]
[TD]51581[/TD]
[TD]6/6/2014[/TD]
[TD]1[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD]162.45[/TD]
[TD][/TD]
[TD]8.40[/TD]
[/TR]
[TR]
[TD]51582[/TD]
[TD]6/6/2014[/TD]
[TD]1[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD]162.45[/TD]
[TD][/TD]
[TD]8.40[/TD]
[/TR]
[TR]
[TD]82882[/TD]
[TD]6/6/2014[/TD]
[TD]2[/TD]
[TD]294.99[/TD]
[TD]336.18[/TD]
[TD]319.40[/TD]
[TD][/TD]
[TD]16.78[/TD]
[/TR]
[TR]
[TD]87491[/TD]
[TD]6/6/2014[/TD]
[TD]1[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD]162.45[/TD]
[TD][/TD]
[TD]8.40[/TD]
[/TR]
[TR]
[TD]88085[/TD]
[TD]6/6/2014[/TD]
[TD]1[/TD]
[TD]149.92[/TD]
[TD]170.85[/TD]
[TD]162.45[/TD]
[TD][/TD]
[TD]8.40[/TD]
[/TR]
</tbody>[/TABLE]



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
 
Sort by date Sort by votes
Why an array formula as opposed to a just a lookup?

=lookup(1, condition1/condition2,returnRange)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Multiple a dynamic Range based on conditions
Replies
0
Views
914
Pepperoni
P
M
  • Question Question
ROW()/COLUMN() Function Formula Equivalent for an Array Created by Setting Two Distinct Ranges Equal to Each Other?
Replies
3
Views
489
Peter_SSs
Peter_SSs
L
  • Question Question
Multi-conditional Index/Match Function
Replies
1
Views
973
CalcSux78
CalcSux78
D
  • Question Question
Array Formula
Replies
4
Views
487
ddd2009
D
M
  • Question Question
Help Converting Multi Condition Sum(IF(...)) Array Formula Into VBA Syntax
2
Replies
18
Views
2K
WarPigl3t
WarPigl3t

Share this page

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