Changing the rows while comparing based on a condition

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Sheet1

ABCDEFGH
1 1st Loan Estimate 2nd Loan Estimate 3rd Loan Estimate Actual Charge
2 IBL IBL IBL
3
4Section A
5 496 458 417 493
6 411 418 482 489
7 449 466 434 463
8 425 447 419 498
9Section B
10 226 200 202 206
11 217 210 200 217
12 228 248 223 233
13 240 242 232 230
14Section C
15 353 319 315 348
16 396 381 367 392
17 354 332 386 307
18 347 358 321 333

<colgroup><col style="width: 30px;"><col style="width: 78px;"><col style="width: 105px;"><col style="width: 64px;"><col style="width: 121px;"><col style="width: 64px;"><col style="width: 117px;"><col style="width: 64px;"><col style="width: 93px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D2{=IF(OR(D5:D13>B5:B13,SUM(D15:D18)>SUM(B15:B18)*1.1),"IBL","IWL")}
F2{=IF(OR(F5:F13>D5:D13,SUM(F15:F18)>SUM(D15:D18)*1.1),"IBL","IWL")}
H2{=IF(OR(H5:H13>F5:F13,SUM(H15:H18)>SUM(F15:F18)*1.1),"IBL","IWL")}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




I have a formula in D2 which compares my data in D5:D13 with B5:B13 individually. If there is an increase beyond tolerance in the values, the return value in D2 is "IBL" which means (Increased beyond Limit). I have put the same formula in F2 and H2. As per the formula the column H is compared with F and F is compared with D and D is compared with B.
If there is no increase beyond tolerance it returns me IWL which means (Increased within tolerance)

Till here everything is fine. The difficult part comes here. The formula is calculating everything perfectly.
But now I want a change that I am not able to do. The change is as follows:
The column H is compared with F, but if F2 is IWL then instead of comparing with column F the comparison should be made with column D. if D2 also has IWL then the comparison should be made with Column B. The shifting of the formula is what i find challenging. Please advise.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Please advise if I need to clarify my question even more.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,752
Messages
5,626,661
Members
416,199
Latest member
Gautamsunil

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
Top