You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Help! I've got a table with a list of project values. I want to show which value band each project falls in.
So for a £2,450,000 project it would show in an adjacentcell £0-£3M.
Value bands are 0-3, 3-6, 6-10, 10-15, 15-20, £20M+
I've had a play with MATCH and VLOOKUP but can't get these to work. Pivot tables scare me. Nested if/then/else will be complicated with that many ranges. Any suggestions?
Conditional formatting?
Conditional formatting +1
You can do this with VLOOKUP(). Send me an email, OP and I'll send you a workbook.
I can't be arsed to try and explain it here :).
Rob, many thanks for your help. VLOOKUP works a treat!
This way is pretty fast if you are constantly changing the grouping banding values
C1=Lowest Band Value next band in D1, then E1, F1 & G1 the highest
<table style="border-collapse: collapse; width: 240pt;" border="0" width="320" cellspacing="0" cellpadding="0">
<tbody>
<tr style="height: 14.4pt;">
<td class="xl64" style="height: 14.4pt; width: 48pt; font-size: 8pt; color: windowtext; font-family: 'Arial Narrow'; border: none; background: #63be7b;" align="right" width="64" height="19">£1</td>
<td class="xl64" style="width: 48pt; font-size: 8pt; color: windowtext; font-family: 'Arial Narrow'; border: none; background: #82c77c;" align="right" width="64">£1,000</td>
<td class="xl64" style="width: 48pt; font-size: 8pt; color: windowtext; font-family: 'Arial Narrow'; border: none; background: #ffeb84;" align="right" width="64">£5,000</td>
<td class="xl64" style="width: 48pt; font-size: 8pt; color: windowtext; font-family: 'Arial Narrow'; border: none; background: #fed480;" align="right" width="64">£50,000</td>
<td class="xl64" style="width: 48pt; font-size: 8pt; color: windowtext; font-family: 'Arial Narrow'; border: none; background: #f8696b;" align="right" width="64">£250,000</td>
</tr>
</tbody>
</table>
Table of values column C3
and copy in
=IF(C3>C$1,IF(C3>D$1,IF(C3>E$1,IF(C3>E$1,IF(C3>F$1,IF(C3>G$1,"£"&TEXT(G$1,"#,##0")&"+","£"&TEXT(F$1,"#,##0")&">£"&TEXT(G$1,"#,##0")),"£"&TEXT(E$1,"#,##0")&">£"&TEXT(F$1,"#,##0")),"£"&TEXT(D$1,"#,##0")&"+"),"£"&TEXT(D$1,"#,##0")&">£"&TEXT(E$1,"#,##0")),"£"&TEXT(C$1,"#,##0")&">£"&TEXT(D$1,"#,##0")),"£000>£"&TEXT(C$1,"#,##0"))
This will produce its own labels against each value in column C
Rather than nested if blocks etc you could just use the IFS function which takes multiple conditions and returns the value of the first one that's true.