Only return value and multiply if criterias are true

P

PostIt

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

[TABLE="width: 944"]
<tbody>[TR]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]gram[/TD]
[TD="colspan: 2"]silver/gram(w/10% loss)[/TD]
[TD="colspan: 3"]silver price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]silver weight[/TD]
[TD]1.26[/TD]
[TD="colspan: 2"]0.62[/TD]
[TD="colspan: 3"]0.32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]total weight[/TD]
[TD]1.63[/TD]
[TD="colspan: 2"]silver market/oz[/TD]
[TD="colspan: 3"]$17.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]no[/TD]
[TD]stone[/TD]
[TD]q'ty[/TD]
[TD]stone unit price (+10% loss)[/TD]
[TD]stone sub-total[/TD]
[TD]setting unit price[/TD]
[TD]setting sub-total[/TD]
[TD="colspan: 2"]adjusted stone + setting[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RD 1.4[/TD]
[TD]10[/TD]
[TD]0.007[/TD]
[TD]0.07[/TD]
[TD]0.02[/TD]
[TD]0.2[/TD]
[TD="colspan: 2"]0.15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RD 5.0[/TD]
[TD]2[/TD]
[TD]0.199[/TD]
[TD]0.398[/TD]
[TD]0.05[/TD]
[TD]0.1[/TD]
[TD="colspan: 2"]0.03[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]total[/TD]
[TD="colspan: 2"]0.468[/TD]
[TD="colspan: 2"]0.3[/TD]
[TD="colspan: 2"]0.18[/TD]
[/TR]
[TR]
[TD="colspan: 6"][/TD]
[TD]adjusted total[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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>
Click to expand...
 
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
[TABLE="class: cms_table, width: 944"]
<tbody>[TR]
[TD]no[/TD]
[TD]stone[/TD]
[TD]q'ty[/TD]
[TD]stone unit price (+10% loss)[/TD]
[TD]stone sub-total[/TD]
[TD]setting unit price[/TD]
[TD]setting sub-total[/TD]
[TD="colspan: 2"]adjusted stone + setting[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RD 1.4[/TD]
[TD]10[/TD]
[TD]0.007[/TD]
[TD]0.07[/TD]
[TD]0.02[/TD]
[TD]0.2[/TD]
[TD="colspan: 2"]0.15[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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.

[TABLE="class: cms_table, width: 944"]
<tbody>[TR]
[TD]no[/TD]
[TD]stone[/TD]
[TD]q'ty[/TD]
[TD]stone unit price (+10% loss)[/TD]
[TD]stone sub-total[/TD]
[TD]setting unit price[/TD]
[TD]setting sub-total[/TD]
[TD="colspan: 2"]adjusted stone + setting[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RD 5.0[/TD]
[TD]2[/TD]
[TD]0.199[/TD]
[TD]0.398[/TD]
[TD]0.05[/TD]
[TD]0.1[/TD]
[TD="colspan: 2"]0.03[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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!
 
Upvote 0
First you'd have to set up a table where you have your intervals and weight listed:[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]bottom[/TD]
[TD]ceiling[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]0.09[/TD]
[TD]2[/TD]
[TD]0.015[/TD]
[/TR]
[TR]
[TD]2.0001[/TD]
[TD]3[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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 ,.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
437
etaf
etaf
S
  • Question Question
Streamlining a long, manual formula
Replies
1
Views
910
SewStage
S
G
  • Question Question
Find Values that Match Criteria from Different Sheet and Copy over to Another
Replies
0
Views
369
glaccounting
G
Ironman
  • Question Question
Review of Application.EnableEvents = False/True lines in worksheet_change event
Replies
1
Views
2K
jasonb75
J
N
  • Question Question
Comparing and returning values based on multiple criteria
Replies
2
Views
475
Brunts
B

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top