Hi,

Not sure if this would be an 'IF' or a 'ROUNDUP'?

If a number is between 0.00 and 49.99 – round up to the nearest 25p
If 50 to 249.99 – round up to the nearest £1


If 250 to 499.99 – round up to the nearest £5
If 500 to 999.99 – round up to the nearest £10
If 1000+ - round to the nearest £25

This will need to be one very long question rather than the 5 bands I’ve broken it down into

Hi and welcome


This seems to work =CEILING(I9,FLOOR(I9/50,1)+(0.25*(I9<50))+5*(I9>=1000))


(replace I9 with your cell ref)
In the future please add a small sample sheet ( no pics please) with desired results and some data

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for the reply @Pecoflyer

I've tried using this formula, however it doesn't appear to be working as I expected. Some of the sums do, but then the larger numbers are skewed. Any ideas? I was wondering if this would be an 'IF' rather than 'CEILING'?

Trying to add an example but don't know how?

You might want to try this one

=IFS(A1<50,CEILING(A1,0.25),A1<250,CEILING(A1,1),A 1<450,CEILING(A1,5),A1<1000,CEILING(A1,10),A1>=100 0,CEILING(A1,25))

Sorry one more question! A colleague has a different version of Excel (2016) and IFS is not available and returns an error - any way round this?

Another possible solution!

Attached Files

RoundupExample.xlsx (8.5 KB, 8 views)

Here is same formula using only one (and fixed, not dynamic) named range - it is easier to edit this one.

Attached Files

RoundupExample.xlsx (8.5 KB, 11 views)

=if(a1<50,ceiling(a1,0.25),if(a1<250,ceiling(a1,1) ,if(a1<450,ceiling(a1,5),if(a1<1000,ceiling(a1,10) ,if(a1>=1000,ceiling(a1,25))))))