Formula for range either plus or minus

[Caly]

Hello I have a list of items where there is a price difference. But I want to remove items that have a change of 5% but either plus or minus. How can I write this as a formula?

So if there is a price difference if -5% or at a positive 5% and any range within that (so -5% up to 5%), how can I write the formula to remove items if they are within that range? But I want to hold anything that is day -6%. So a min of -5% and a max of 5%.

 

[Joe4]

Please show us an example of your data (where we can see where these prices you are comparing exist) and your expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Caly]

Thank you very much. Here is an example of the data set. First column has the differnence in price change by percent and the second column has the desired result

Price change	Determination
-5%	Remove
-5.30%	Hold
4.50%	Remove
7%	Hold

 

[Joe4]

I understand the concept, but we need to understand the structure of your data.
Otherwise, I could give you a formula (or just tell you to use the ABS function), but that may not actually do what you want based on your data structure and desired outcome.

You have listed 5 different of Office in your Office version. Which one are you really using here?
Excel 365 has some really cool new formula options that may help, but we don't want to suggest them if that is not what you are using here.

How exactly is your data laid out?
Do you just have one column of data (column A) that shows the Price Change and no other related data?
Is the change already calculated, or are there multiple columns of data, and the price change needs to be calculated?

Also, you say "formula", but an Excel formula cannot delete data.
We can either have formulas in other locations that returns a new list based on your original.
Or we can use VBA code to physically delete rows of data that meet those requirements.
So which option would you prefer?

So you see, there is a level of detail that is needed if you would like a solution that is tailored to your specific structure and needs.
If you provide the answers to the question is red, you stand a much better chance of getting a solution that will work exactly the way you want/need it to.

 

[Caly]

Thank you , apologies I should have provided these details. Thank you. I am using Office excel 365.

These are the starting and ending prices followed by the price change of the new vs the starting.

I’m looking to not have excel remove anything but just show in words the suggested action. This is in column D for the determination.

Col A. Col B. Col C. Col D

Starting price	New price	Price change	Determination
423.49	400.37	-5.46	Hold
3.68	3.60	-2.40%	Remove

 

[Peter_SSs]

Like this?

23 08 02.xlsm
	A	B	C	D
1	Starting price	New price	Price change	Determination
2	423.49	400.37	-5.46%	Hold
3	3.68	3.6	-2.40%	Remove
Determination

Cell Formulas
Range	Formula
D2:D3	D2	=IF(ABS(C2:C3)>0.05,"Hold","Remove")
Dynamic array formulas.

 

[Caly]

Thank you. This is great and worked perfectly. Thank you

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the confirmation.