Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-23-2019, 08:27 AM
MSG75 MSG75 is offline Windows XP Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
MSG75 is on a distinguished road
Default Any help on a formula for this?

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
Reply With Quote
  #2  
Old 01-23-2019, 09:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,294
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 01-24-2019, 02:47 AM
MSG75 MSG75 is offline Windows XP Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
MSG75 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 01-24-2019, 03:53 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 962
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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))
Reply With Quote
  #5  
Old 01-24-2019, 05:13 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 386
ArviLaanemets will become famous soon enough
Default

Another possible solution!
Attached Files
File Type: xlsx RoundupExample.xlsx (8.5 KB, 0 views)
Reply With Quote
  #6  
Old 01-24-2019, 05:23 AM
ArviLaanemets ArviLaanemets is online now Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 386
ArviLaanemets will become famous soon enough
Default

Here is same formula using only one (and fixed, not dynamic) named range - it is easier to edit this one.
Attached Files
File Type: xlsx RoundupExample.xlsx (8.5 KB, 3 views)
Reply With Quote
  #7  
Old 01-28-2019, 08:40 AM
MSG75 MSG75 is offline Windows XP Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
MSG75 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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?
Reply With Quote
  #8  
Old 01-28-2019, 09:50 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 962
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

=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))))))
Reply With Quote
Reply

Tags
#excel, #formula, #help!

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
If or formula help zhead Excel 8 04-25-2018 07:09 AM
Formula Copy Row 2 Row But Next Column In Formula From Another Tab TimG Excel 3 04-16-2018 09:20 PM
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
Formula Help OTPM Excel 2 05-22-2013 06:28 AM


All times are GMT -7. The time now is 05:46 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft