Only return value and multiply if criterias are true

PostIt

New Member
Joined
Jun 3, 2015
Messages
11
Running Windows 10
Microsoft Office Home and Student 2010 Excel

HIJKLMNOP
gramsilver/gram(w/10% loss)silver price
silver weight1.260.620.32
total weight1.63silver market/oz$17.50
nostoneq'tystone unit price (+10% loss)stone sub-totalsetting unit pricesetting sub-totaladjusted stone + setting
1RD 1.4100.0070.070.020.20.15
2RD 5.020.1990.3980.050.10.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
total0.4680.30.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>
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

bilgejan

New Member
Joined
Apr 5, 2016
Messages
7
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

New Member
Joined
Jun 3, 2015
Messages
11
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

New Member
Joined
Apr 5, 2016
Messages
7
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

New Member
Joined
Jun 3, 2015
Messages
11

ADVERTISEMENT

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

Active Member
Joined
Nov 18, 2015
Messages
467
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

New Member
Joined
Jun 3, 2015
Messages
11
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.
nostoneq'tystone unit price (+10% loss)stone sub-totalsetting unit pricesetting sub-totaladjusted stone + setting
1RD 1.4100.0070.070.020.20.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.

nostoneq'tystone unit price (+10% loss)stone sub-totalsetting unit pricesetting sub-totaladjusted stone + setting
2RD 5.020.1990.3980.050.10.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

Active Member
Joined
Nov 18, 2015
Messages
467
First you'd have to set up a table where you have your intervals and weight listed:
bottomceilingvalue
0.0920.015
2.000130.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 ,.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,272
Messages
5,527,712
Members
409,785
Latest member
lalz1205

This Week's Hot Topics

Top