Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2019, 08:27 AM
MSG75 MSG75 is offline Any help on a formula for this? Windows XP Any help on a formula for this? Office 2016
Novice
Any help on a formula for this?
 
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 online now Any help on a formula for this? Windows 7 64bit Any help on a formula for this? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
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
Reply With Quote
  #3  
Old 01-24-2019, 02:47 AM
MSG75 MSG75 is offline Any help on a formula for this? Windows XP Any help on a formula for this? Office 2016
Novice
Any help on a formula for this?
 
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 Any help on a formula for this? Windows 10 Any help on a formula for this? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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 offline Any help on a formula for this? Windows 8 Any help on a formula for this? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Another possible solution!
Attached Files
File Type: xlsx RoundupExample.xlsx (8.5 KB, 6 views)
Reply With Quote
  #6  
Old 01-24-2019, 05:23 AM
ArviLaanemets ArviLaanemets is offline Any help on a formula for this? Windows 8 Any help on a formula for this? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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, 9 views)
Reply With Quote
  #7  
Old 01-28-2019, 08:40 AM
MSG75 MSG75 is offline Any help on a formula for this? Windows XP Any help on a formula for this? Office 2016
Novice
Any help on a formula for this?
 
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 Any help on a formula for this? Windows 10 Any help on a formula for this? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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
Any help on a formula for this? 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
Any help on a formula for this? Formula Help OTPM Excel 2 05-22-2013 06:28 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft