Forum Discussion

Brass Contributor

Jun 25, 2025

Solved

Volume range, apply % in formula

Hello. I am working on figuring out a formula that I can apply in excel. The logic is basically, if the volume is within a specific range, then the formula is X * (1- %). I have attached a example...

excel

SergeiBaklan
Jun 25, 2025
Yes, it works. In your sample there are values within the range. In general LOOKUP function - Microsoft Support

However, it'll be an error if the volume value is less than 100000. If that's the case, add to your volume ranges the one from zero to 100000. Another point, volume ranges are to be in ascending order.

To be more flexible you may use XLOOKUP function - Microsoft Support, variant for it is attached file.
Prices_COGS.xlsx14 KB

Maddy1010

Brass Contributor

Jun 25, 2025

I actually did not phrase this correctly. Please see attached file instead.

Prices_COGS.xlsx12 KB

SergeiBaklan

Diamond Contributor

Jun 25, 2025

There is no attachment. You may share through OneDrive, Google Drive, etc if attachment option is not available here. In general like

= IF( (V>=Rmin)*(V<=Rmax), X*(1-%), X )

Maddy1010
Brass Contributor
Jun 25, 2025
SergeiBaklan pls find attached excel file.
Prices_COGS.xlsx12 KB
- SergeiBaklan
  Diamond Contributor
  Jun 25, 2025
  That could be
  
  =LOOKUP(F2,$B$2:$B$16, $D$2:$D$16*(1-$C$2:$C$16) )
  
  Please check attached
  Prices_COGS.xlsx14 KB
  - Maddy1010
    Brass Contributor
    Jun 25, 2025
    Thank you SergeiBaklan . However, with the lookup formula - my lookup value has to match exactly. What if the value I'm looking up is only "within the range"? Can this formula also apply? Thank you!