Only return value and multiply if criterias are true

[PostIt]

Running Windows 10
Microsoft Office Home and Student 2010 Excel

H	I	J	K	L	M	N	O	P
			gram	silver/gram(w/10% loss)	silver price
	silver weight	1.26	0.62	0.32
	total weight	1.63	silver market/oz	$17.50
no	stone	q'ty	stone unit price (+10% loss)	stone sub-total	setting unit price	setting sub-total	adjusted stone + setting
1	RD 1.4	10	0.007	0.07	0.02	0.2	0.15
2	RD 5.0	2	0.199	0.398	0.05	0.1	0.03
3			#N/A	#N/A	#N/A	#N/A
4			#N/A	#N/A	#N/A	#N/A
5			#N/A	#N/A	#N/A	#N/A
6			#N/A	#N/A	#N/A	#N/A
7			#N/A	#N/A	#N/A	#N/A
8			#N/A	#N/A	#N/A	#N/A
9			#N/A	#N/A	#N/A	#N/A
10			#N/A	#N/A	#N/A	#N/A
11			#N/A	#N/A	#N/A	#N/A
12			#N/A	#N/A	#N/A	#N/A
13			#N/A	#N/A	#N/A	#N/A
14			#N/A	#N/A	#N/A	#N/A
15			#N/A	#N/A	#N/A	#N/A
16			#N/A	#N/A	#N/A	#N/A
17			#N/A	#N/A	#N/A	#N/A
18			#N/A	#N/A	#N/A	#N/A
19			#N/A	#N/A	#N/A	#N/A
20			#N/A	#N/A	#N/A	#N/A
		total	0.468	0.3	0.18
	adjusted total

<tbody>
</tbody>

In the "adjusted stone + setting" column, I have the following formula in that column.

=PRODUCT(J7*(IF(I7<"RD 2.0">I7>"RD 0.90",0.015,OR(IF(I7>"RD 2.10"<i7<"rd quote]
I've been trying to get the formula to only multiply the q'ty by $0.015 if the stone is greater than/= RD 0.09 and less than/= RD 2.0 OR by $0.02 if the stone is greater than/= RD ​2.1 and less than/= to RD 3.0.
If none are true then the formula should add stone sub-total and setting sub-total.

I can't get this to work. Please help.</i7<"rd>

 

[bilgejan]

Can you change the colomn name "stone" to "stone RD"?

this way you can start using your formula; because the values you input are now numbers.

if you still need to see RD in front of the values, you can create a custom Currency symbol.

 

[PostIt]

The only problem with that would be that RD is not a set stone. There are many shapes and sizes, such as TP 4*6*7 and CS 4.0.
I need the formula in the adjusted stone + setting" column to only return a value if it meets the aforementioned criteria.
If not, then the formula should just add stone sub-total and setting sub-total.
<strike></strike>
I'm not sure if that makes any sense.

 

[bilgejan]

well, not knowing what RD stood for was a hiccup. in case RD part is also a variable it is a whole new story. the formula you wrote does not make any sense (to me), since it does not first check if the number value is type RD.

in this case normal approach should be adding another colomn for RD/TP/CS... then you can sequentially formulate your criteria. (if type is RD and bigger than 0.5 and on...)

but if you really must keep two variable "text" in the same cell and do calculations using ">" and "<"; you also must somehow(logically) introduce this set of data to excel so that it can do such a comparison using these values.

i believe that individuals with such set of skills exist on this platform. wish you the best of luck. cheers.

 

[PostIt]

I'm sorry. I should've explained myself better.
I was just trying to limit the q'ty of columns since everything has to fit on a letter sized sheet and be legible at the same time.
RD is a variable and so is the number that follows, neither is fixed and per set of data, those may change.
All the stone prices, setting prices, silver weight, and total weight are set using vlookup.
The information is brought over from a couple of other workbooks so I can't split the stone column into 2 unless I figure out how to pull over the stone prices following the criteria that the shape and size match whatever is input into those columns and rows.

Thank you for the luck. I hope someone is able to help me soon.

 

[Noodleski]

I don't understand your way of thinking. If you say that RD 2.0 is not set, since there are many possible combinations, how are you going to put them into an excel formula, since there's a limit to nesting formulae?
If the multiplier is determined only by the numeric part at the end, we could split that from the cell using a the right function (presumably there's always a space between the textual part and the numeric part of the cell?)
If the multiplier is depending on the combination of the text code and the numeric part of the cell, you need something to make the logic clearer, because you won't fit that into a single formula.

 

[PostIt]

If the stone is greater than/= RD 0.09 AND less than/= RD 2.0, the returned value should come out to 0.015.
OR if the stone is greater than/= RD ​2.1 AND less than/= to RD 3.0, the returned value should come out to 0.02.
So based off of the returned values, if either criteria is true then the returned value has to be multiplied by the number in the q'ty column.

no	stone	q'ty	stone unit price (+10% loss)	stone sub-total	setting unit price	setting sub-total	adjusted stone + setting
1	RD 1.4	10	0.007	0.07	0.02	0.2	0.15

<tbody>
</tbody>

Since the above is true, with RD 1.4 being in between RD 0.9 ~ RD 2.0, the returned value is 0.015.
I was trying to fit a formula into 1 column so it would essentially be the returned value (0.015) * the q'ty (10), making it show as 0.15.

no	stone	q'ty	stone unit price (+10% loss)	stone sub-total	setting unit price	setting sub-total	adjusted stone + setting
2	RD 5.0	2	0.199	0.398	0.05	0.1	0.03

<tbody>
</tbody>

The above is false, with RD 5.0 not meeting the criteria mentioned at the top.
So I wanted the formula to also be able to add the stone sub-total (0.398) to the setting sub-total (0.1) if the stone didn't meet the criteria.
Making the outcome be 0.498.

I'm not sure if that made more sense or if I'm just being unrealistic.
Thank you!

 

[Noodleski]

First you'd have to set up a table where you have your intervals and weight listed:

bottom	ceiling	value
0.09	2	0.015
2.0001	3	0.02

<tbody>
</tbody>

You can add intervals as you'd like.
Then have the formula entered like so:

IFERROR(VLOOKUP(RIGHT(I5,LEN(I5)-LOOKUP(" ",I5,1))*1,$S$1:$U$4,3),0)

This assumes that the above table is in S1 to U4 and that your decimal separator is . rather than ,.