Mround/roundup

M

maywal

New Member
Joined
Dec 19, 2016
Messages
29
I am using the following formula to round costs to fit into their relevant fee structure.

=IF($G68<250000,ROUNDUP($G68,-4),IF($G68<1000000,MROUND(ROUNDUP($G68,-4),50000),IF($G68<1400000,MROUND(ROUNDUP($G68,-4),50000),IF($G68<1600000,MROUND(ROUNDUP($G68,-5),100000),MROUND(ROUNDUP($G68,-5),200000)))))

For example, if the cost is £315,000 I need it to round to £350,000 so that it can find the fee for a £350,000 house in a standardised fee sheet. However, this is not rounding. Instead it is rounding down to £300,000.

As you can see the fee sheet scales in different ways as the costs increase, hence why I have set the formula as such. However, need to understand why it is rounding down not up and also if there is an easier way to do this?

Seems to be working for all other figures.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
maywal, Good afternoon.

Maybe I'm missing something.
Why you need to round value to FIT to the FEE SHEET?

Please, show us your FEET SHEET and how you need to do calculations.
I'm sure that will be some people trying help you.
 
Upvote 0
So all must round to match those within the £ column so that I can match them with a fee scale for one of the companies along the top.



[TABLE="class: grid, width: 506"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]£[/TD]
[TD]HSBC/FD[/TD]
[TD]YBS/ACC[/TD]
[TD]LBG[/TD]
[TD]YTS BTL[/TD]
[TD]COVENTRY[/TD]
[TD]NATIONWIDE[/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]15000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]25000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]30000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]40000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]45000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]50000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]50000[/TD]
[/TR]
[TR]
[TD="align: right"]55000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]60000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]65000[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]70000[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]75000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]80000[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]85000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]90000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]95000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]100000[/TD]
[/TR]
[TR]
[TD="align: right"]105000[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]110000[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]115000[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]120000[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]125000[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]130000[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]135000[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]140000[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]150000[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]150000[/TD]
[/TR]
[TR]
[TD="align: right"]160000[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]175000[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]170000[/TD]
[TD="align: right"]180000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]175000[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]180000[/TD]
[TD="align: right"]180000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]190000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]210000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[/TR]
[TR]
[TD="align: right"]220000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[/TR]
[TR]
[TD="align: right"]230000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[/TR]
[TR]
[TD="align: right"]240000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[/TR]
[TR]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]250000[/TD]
[/TR]
[TR]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]300000[/TD]
[/TR]
[TR]
[TD="align: right"]350000[/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]350000[/TD]
[TD="align: right"]350000[/TD]
[/TR]
[TR]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]400000[/TD]
[/TR]
[TR]
[TD="align: right"]450000[/TD]
[TD="align: right"]450000[/TD]
[TD="align: right"]450000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]450000[/TD]
[TD="align: right"]450000[/TD]
[TD="align: right"]450000[/TD]
[/TR]
[TR]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]500000[/TD]
[/TR]
[TR]
[TD="align: right"]550000[/TD]
[TD="align: right"]550000[/TD]
[TD="align: right"]550000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]550000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[/TR]
[TR]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]600000[/TD]
[/TR]
[TR]
[TD="align: right"]650000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]650000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]650000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[/TR]
[TR]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]700000[/TD]
[/TR]
[TR]
[TD="align: right"]750000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]750000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]750000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]800000[/TD]
[/TR]
[TR]
[TD="align: right"]800000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"]800000[/TD]
[/TR]
[TR]
[TD="align: right"]850000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]850000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]850000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]900000[/TD]
[/TR]
[TR]
[TD="align: right"]900000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]900000[/TD]
[/TR]
[TR]
[TD="align: right"]950000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]950000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]950000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[/TR]
[TR]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]1200000[/TD]
[TD="align: right"]1250000[/TD]
[TD="align: right"]1200000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1200000[/TD]
[TD="align: right"]1200000[/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]1250000[/TD]
[TD="align: right"]1250000[/TD]
[TD="align: right"]1250000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1500000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1400000[/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]1500000[/TD]
[TD="align: right"]1500000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]1600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]1600000[/TD]
[TD="align: right"]2000000[/TD]
[/TR]
[TR]
[TD="align: right"]1800000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1800000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]1800000[/TD]
[TD="align: right"]1800000[/TD]
[TD="align: right"]2000000[/TD]
[/TR]
[TR]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[/TR]
[TR]
[TD="align: right"]2200000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2200000[/TD]
[TD="align: right"]2500000[/TD]
[/TR]
[TR]
[TD="align: right"]2400000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2400000[/TD]
[TD="align: right"]2500000[/TD]
[/TR]
[TR]
[TD="align: right"]2600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2600000[/TD]
[TD="align: right"]3000000[/TD]
[/TR]
[TR]
[TD="align: right"]2800000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2800000[/TD]
[TD="align: right"]3000000[/TD]
[/TR]
[TR]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3000000[/TD]
[TD="align: right"]3000000[/TD]
[/TR]
[TR]
[TD="align: right"]3200000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3200000[/TD]
[TD="align: right"]3500000[/TD]
[/TR]
[TR]
[TD="align: right"]3400000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3400000[/TD]
[TD="align: right"]3500000[/TD]
[/TR]
[TR]
[TD="align: right"]3600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3600000[/TD]
[TD="align: right"]4000000[/TD]
[/TR]
[TR]
[TD="align: right"]3800000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]3800000[/TD]
[TD="align: right"]4000000[/TD]
[/TR]
[TR]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]4000000[/TD]
[/TR]
[TR]
[TD="align: right"]4200000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4200000[/TD]
[TD="align: right"]4500000[/TD]
[/TR]
[TR]
[TD="align: right"]4400000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4400000[/TD]
[TD="align: right"]4500000[/TD]
[/TR]
[TR]
[TD="align: right"]4600000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4600000[/TD]
[TD="align: right"]5000000[/TD]
[/TR]
[TR]
[TD="align: right"]4800000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4800000[/TD]
[TD="align: right"]5000000[/TD]
[/TR]
[TR]
[TD="align: right"]5000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]4000000[/TD]
[TD="align: right"]2000000[/TD]
[TD="align: right"]5000000[/TD]
[TD="align: right"]5000000[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
maywal, Good afternoon.

I believe that VLOOKUP function and MATCH function can SOLVE your question.

Suppose your FEE TABLE at E1:K76

Suppose:

A2 --> COMPANY NAME
B2 --> VALUE

Try to use:

C2 --> FORMULA -->
Code: 
=VLOOKUP(B2, E2:K76, MATCH(A2, E1:K1, 0), TRUE)
Please, do your tests and tell us if it worked as you want.

I hope it helps.
 
Upvote 0
@maywal.... Replace MROUND(ROUNDUP($G68,-4),50000) with ROUNDUP($G68/50000,0)*50000. Maybe also ROUNDUP($G68/100000,0)*100000 and ROUNDUP($G68/200000,0)*200000.

Aside.... I wonder if you have a mistake. You roundup to a multiple of 50000 when G68 is both less than 1000000 and less than 1400000. If that is correct, it is sufficient to have one condition, to wit: $G68 < 1400000. But I wonder if the result of the second condition should be ROUNDUP($G68,75000,0)*75000.
 
Upvote 0
Hello,

Thank you for your message.

I don't think this will work as I am using a VLOOKUP for the second step of this. Let me explain:

In 'sheet 1' - I am storing the task, the price of the task and other relevant information. The price of the task could be any number. Such as my example above £315,000. In column E I take the price of the task and I round it using the formula above. The reason this needs to round is so that it can match the sheet above. Each of the companies identified above HSBC etc. have their own sheet. I then use the rounded number to VLOOKUP in each of the sheets.

I want to attach a copy of this workbook but I can't seem to find the attachment option. If you could advise on how to do this I think it will make a lot more sense.
 
Upvote 0
I want to attach a copy of this workbook but I can't seem to find the attachment option. If you could advise on how to do this I think it will make a lot more sense.
Click to expand...
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Some people also upload files to file sharing sites and provide links to it here, but understand that many people here cannot or will not download files from the internet for security reasons.
 
Upvote 0
This array formula should get it:

=MIN(IF(A1:A76>=I1,A1:A76))

with your value in I1.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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