Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Using IF Function where logical test is for range of values Using IF Function where logical test is for range of values
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 02-10-2017, 02:29 PM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default Using IF Function where logical test is for range of values
Hi,



Can someone help me with this formula, please:

A customer gets a rebate once he reached a specific amount of sales:

over $200000 (and until 299999) 2%, when the customer reached $300000 then he gets a rebate of 4% back of everything over $200000.

I tried ( but I am a newbie ) :

=IF(G21>=200000,G21*0.02,IF(G21>=300000,G21*0.04))

Thanks in advance
Reply With Quote
  #2  
Old 02-11-2017, 12:56 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Using IF Function where logical test is for range of values Windows 7 64bit Using IF Function where logical test is for range of values Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Hi
is the 2% rebate from 200K to 300 k or from 0 to 300k ( if sale >200k) ?
__________________
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
Reply With Quote
  #3  
Old 02-11-2017, 06:53 AM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default
Hi ,

it is over $200000, no discounts under $200000

$200000 - 299999 2%

but if the customer reached $300000 then he gets 4% back for everything over $200000

Thanks
Reply With Quote
  #4  
Old 02-11-2017, 07:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Using IF Function where logical test is for range of values Windows 7 64bit Using IF Function where logical test is for range of values Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Perhaps =IF(A1>=300000,(A1-200000)*0.96+200000,MAX(0,A1-200000)*0.98+MIN(A1,200000))
where A1 contains the sale
__________________
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
Reply With Quote
  #5  
Old 02-11-2017, 01:54 PM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default
Quote:
Originally Posted by Pecoflyer View Post
Perhaps =IF(A1>=300000,(A1-200000)*0.96+200000,MAX(0,A1-200000)*0.98+MIN(A1,200000))
where A1 contains the sale
=IF(G21>=300000,(G21-200000)*0.96+200000,MAX(0,G21-200000)*0.98+MIN(G21,200000))

This shows me Err:508
Reply With Quote
  #6  
Old 02-11-2017, 12:12 PM
xor xor is offline Using IF Function where logical test is for range of values Windows 10 Using IF Function where logical test is for range of values Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
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
How about:

=IF(A1<200000,"",IF((A1>=200000)*(A1<300000),0.02, 0.04)*(A1-200000))
Reply With Quote
  #7  
Old 02-11-2017, 01:58 PM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default
Quote:
Originally Posted by xor View Post
How about:

=IF(A1<200000,"",IF((A1>=200000)*(A1<300000),0.02, 0.04)*(A1-200000))
Thank you,

This formula shows me Err:509

=IF(G21<200000,"",IF((G21>=200000)*(G21<300000),0. 02, 0.04)*(G21-200000))
Reply With Quote
  #8  
Old 02-11-2017, 11:07 PM
xor xor is offline Using IF Function where logical test is for range of values Windows 10 Using IF Function where logical test is for range of values Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,100
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
Don't know what you are doing.
Attached Files
File Type: xlsx quciolino.xlsx (14.9 KB, 12 views)
Reply With Quote
  #9  
Old 02-13-2017, 08:05 AM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default
Quote:
Originally Posted by xor View Post
Don't know what you are doing.
Thank you Xor
Reply With Quote
  #10  
Old 02-12-2017, 01:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Using IF Function where logical test is for range of values Windows 7 64bit Using IF Function where logical test is for range of values Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Which are your regional settings?
You will have to adapt the formula eventually if you use European settings
__________________
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
Reply With Quote
  #11  
Old 02-13-2017, 08:03 AM
guciolino guciolino is offline Using IF Function where logical test is for range of values Mac OS X Using IF Function where logical test is for range of values Office for Mac 2011
Novice
Using IF Function where logical test is for range of values 		 
Join Date: Feb 2017
Posts: 6
guciolino is on a distinguished road
Default
Hi Pecoflyer,

my fault, sorry. After I am back at work, your formula works just fine.

Probably my settings at home excel was different.

Thank to everybody for help
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using IF Function where logical test is for range of values Help with Index Function to Select Range of Cells bretyuin Excel 1 02-24-2016 05:11 AM
Using IF Function where logical test is for range of values Using Left function to format a range USAOz Excel 4 09-10-2015 03:00 AM
Using IF Function where logical test is for range of values select a cluster of point from a range based on x and y values sandcharles Excel 5 02-19-2015 06:15 AM
Help me with simple IF stmt to test date within a range tomseeley Excel 4 02-23-2014 02:13 PM
Using IF Function where logical test is for range of values comparing values from a range struct Excel 1 04-01-2011 07:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:00 PM.

Contact Us - Privacy Statement - Top

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