Mround/roundup

[maywal]

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.

 

[Marcílio_Lobão]

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.

 

[maywal]

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.

£	HSBC/FD	YBS/ACC	LBG	YTS BTL	COVENTRY	NATIONWIDE
10000	30000	100000	100000	100000	60000	50000
15000	30000	100000	100000	100000	60000	50000
20000	30000	100000	100000	100000	60000	50000
25000	30000	100000	100000	100000	60000	50000
30000	30000	100000	100000	100000	60000	50000
35000	50000	100000	100000	100000	60000	50000
40000	50000	100000	100000	100000	60000	50000
45000	50000	100000	100000	100000	60000	50000
50000	50000	100000	100000	100000	60000	50000
55000	60000	100000	100000	100000	60000	100000
60000	60000	100000	100000	100000	60000	100000
65000	70000	100000	100000	100000	75000	100000
70000	70000	100000	100000	100000	75000	100000
75000	80000	100000	100000	100000	75000	100000
80000	80000	100000	100000	100000	100000	100000
85000	100000	100000	100000	100000	100000	100000
90000	100000	100000	100000	100000	100000	100000
95000	100000	100000	100000	100000	100000	100000
100000	100000	100000	100000	100000	100000	100000
105000	120000	150000	200000	150000	125000	150000
110000	120000	150000	200000	150000	125000	150000
115000	120000	150000	200000	150000	125000	150000
120000	120000	150000	200000	150000	125000	150000
125000	140000	150000	200000	150000	125000	150000
130000	140000	150000	200000	150000	150000	150000
135000	140000	150000	200000	150000	150000	150000
140000	140000	150000	200000	150000	150000	150000
150000	160000	150000	200000	150000	150000	150000
160000	160000	200000	200000	200000	175000	200000
170000	180000	200000	200000	200000	175000	200000
180000	180000	200000	200000	200000	200000	200000
190000	200000	200000	200000	200000	200000	200000
200000	200000	200000	200000	200000	200000	200000
210000	250000	250000	300000	250000	250000	250000
220000	250000	250000	300000	250000	250000	250000
230000	250000	250000	300000	250000	250000	250000
240000	250000	250000	300000	250000	250000	250000
250000	250000	250000	300000	250000	250000	250000
300000	300000	300000	300000	300000	300000	300000
350000	350000	350000	400000	350000	350000	350000
400000	400000	400000	400000	400000	400000	400000
450000	450000	450000	500000	450000	450000	450000
500000	500000	500000	500000	500000	500000	500000
550000	550000	550000	600000	550000	600000	600000
600000	600000	600000	600000	600000	600000	600000
650000	700000	650000	700000	650000	700000	700000
700000	700000	700000	700000	700000	700000	700000
750000	800000	750000	1000000	750000	800000	800000
800000	800000	800000	1000000	800000	800000	800000
850000	900000	850000	1000000	850000	900000	900000
900000	900000	900000	1000000	900000	900000	900000
950000	1000000	950000	1000000	950000	1000000	1000000
1000000	1000000	1000000	1000000	1000000	1000000	1500000
1200000	1250000	1200000	1400000	1200000	1200000	1500000
1250000	1250000	1250000	1400000	1400000	1400000	1500000
1400000	1500000	1400000	1400000	1400000	1400000	1500000
1500000	1500000	1600000	2000000	1600000	1600000	1500000
1600000	2000000	1600000	2000000	1600000	1600000	2000000
1800000	2000000	1800000	2000000	1800000	1800000	2000000
2000000	2000000	2000000	2000000	2000000	2000000	2000000
2200000	2000000	2000000	3000000	2000000	2200000	2500000
2400000	2000000	2000000	3000000	2000000	2400000	2500000
2600000	2000000	2000000	3000000	2000000	2600000	3000000
2800000	2000000	2000000	3000000	2000000	2800000	3000000
3000000	2000000	2000000	3000000	2000000	3000000	3000000
3200000	2000000	2000000	4000000	2000000	3200000	3500000
3400000	2000000	2000000	4000000	2000000	3400000	3500000
3600000	2000000	2000000	4000000	2000000	3600000	4000000
3800000	2000000	2000000	4000000	2000000	3800000	4000000
4000000	2000000	2000000	4000000	2000000	4000000	4000000
4200000	2000000	2000000	4000000	2000000	4200000	4500000
4400000	2000000	2000000	4000000	2000000	4400000	4500000
4600000	2000000	2000000	4000000	2000000	4600000	5000000
4800000	2000000	2000000	4000000	2000000	4800000	5000000
5000000	2000000	2000000	4000000	2000000	5000000	5000000

<colgroup><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[Marcílio_Lobão]

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 -->

=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.

 

[joeu2004]

@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.

 

[maywal]

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.

 

[Joe4]

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.

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.

 

[steve the fish]

This array formula should get it:

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

with your value in I1.

 

[maywal]

This works brilliantly. Thank you.