Spartanjuli1
New Member
- Joined
- Sep 13, 2016
- Messages
- 13
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I have a challenge for you
In the table below, I would like to have a formula (going from D5 to D19) which is giving me the closest upper "Total Hours" value when the C column is in state "TRUE".
Some formulas were tried, but it was always giving me B17 results instead of B14 cell.
[TABLE="class: grid, width: 519"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Years[/TD]
[TD] Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Total Years[/TD]
[TD]Total Hours[/TD]
[TD]"A5 <= $A$2 ?"[/TD]
[TD]"Closest upper value $B$2"[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]1000[/TD]
[TD]TRUE
[/TD]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]2400[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]3[/TD]
[TD]2250[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]3600[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[TD]4500[/TD]
[TD]TRUE[/TD]
[TD]TRUE (because 4500 is the closest upper value to B2)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]2000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD]3000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4[/TD]
[TD]3700[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4[/TD]
[TD]4800[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[TD]6000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
Thank you a lot !
Julien
****** id="cke_pastebin" style="position: absolute; top: 318px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 519"]
<tbody>[TR]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
</tbody>[/TABLE]
</body>
I have a challenge for you
In the table below, I would like to have a formula (going from D5 to D19) which is giving me the closest upper "Total Hours" value when the C column is in state "TRUE".
Some formulas were tried, but it was always giving me B17 results instead of B14 cell.
- Do you have any ideas ??
[TABLE="class: grid, width: 519"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Years[/TD]
[TD] Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Total Years[/TD]
[TD]Total Hours[/TD]
[TD]"A5 <= $A$2 ?"[/TD]
[TD]"Closest upper value $B$2"[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]1000[/TD]
[TD]TRUE
[/TD]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[TD]2400[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]1500[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]3[/TD]
[TD]2250[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3[/TD]
[TD]3000[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]3600[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[TD]4500[/TD]
[TD]TRUE[/TD]
[TD]TRUE (because 4500 is the closest upper value to B2)[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]2000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4[/TD]
[TD]3000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]4[/TD]
[TD]3700[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]4[/TD]
[TD]4800[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[TD]6000[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
Thank you a lot !
Julien
****** id="cke_pastebin" style="position: absolute; top: 318px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 519"]
<tbody>[TR]
[TD]IF A5 <= $A$2 THEN return closest upper value WHERE (B2 <= B5:B19)[/TD]
[/TR]
</tbody>[/TABLE]
</body>