Need help figuring out a formula to calculate prices based on 4 conditions

ObiSol

New Member
Joined
Sep 11, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello everyone,
I was working on a personal project on which I need to get a value based off of 3 different conditions on different columns.

This is what currently works:
=IF(C2:C=1,P2:P/220,IF(C2:C=2,P2:P/150,IF(C2:C=3,P2:P/50)))

But I need to add one more condition
"C" is the edition of a card that ranges from 1-3
"P" is the amount of people requesting the card

The divisors are the current rate of the market in which we are using with the outcome being the price of the card. So, a card requested by 2,500 people at a rate of 200 would yield a price of 12.5.

The other 2 conditions I need on this formula is to check if the card is a high, mid, or low print. That would determine the rarity of the card.
High Prints are x>=1000, Mid Prints are x<=999~100, and Low Prints are x<=99

A High Print card (x>=1000) would use the rate of 200 if edition 1, 150 if edition 2, 80 if edition 3.
A Mid Print (x<=999 to 100) would use a rate of 50 if edition 1, 40 if edition 2, 30 if edition 3.

A Low Print (x<=99) would return a result of "LF Offers"


Here is the link to the spreadsheet: https://1drv.ms/x/s!AnGr1abH9vLwmk5MMsCzJQRiyL1Q?e=ZQmOLt
The
formulas are on cells AC3 and AD3. However, we can hide every other column other than the Print,Type,Ed., Burn $, WL, Effort, Min, and Max.

The first goal: is to be able to generate a card's price based on the print (Low Print[1-99], Mid Print[100-999], High Print[1000+], edition (1,2,3), and the number of wishlists the card has.

The Print is located in Column B, Edition number on Column C, and Wishlists on Column Q.


The second goal: is to have a minimum recommended price and a maximum recommended price. This will be based on the effort of the card (located Column T). If the card has an effort over 200, increase the card's value by reducing the divisor (current market rate)
(e.g. A high-print Edition 2 card drops with an effort over 200. That card will now be divided by 100 instead of 150 in which the divisor is the current market rate). This will affect both the min and max price of course.

The minimum prices for High and Mid Prints would be:
High Print:
Edition 1: Generate a price of gold based on the "Burn Rate (Column I) +10 gold
Edition 2: Wishlists(WL)/200
Mid Print:
Edition 1: WL/50
Edition 2: WL/40

The maximum prices for High and Mid Print would be:
High Print:
Edition 1: Generate a price of gold based on the Burn Rate (Column I)+20 gold
Edition 2: Wishlists(WL)/200
Mid Print:
Edition 1: WL/20
Edition 2: WL/20

If the effort of the card is 200+, then highlight the cell with the maximum price using condition formatting.



  1. If the edition in the D column is 1 then divide the wishlist number in column D by 220.
    If the edition in the D column is 2 then divide the wishlist number in column D by 150
    If the edition in the D column is 3 then generate text: "LF Offers"

    I want to include another condition in which it does the above but factor in the effort (Column G) into account. This would mean lowering the divisor for the "Max" Column only, since the min price should always be the lowest available. (e.g. divisor being 200 instead of 220 if edition 1; 130 instead of 150 if edition 2).

    I suggested doing this by looking to see whether the data in Column G, "Effort",
    was greater than or equal to 200. If so, then divide by the new different divisor than the standard one.

    Reiterating the example above, this would mean dividing the wishlist number by 130 instead of 150 for edition 2 cards and 200 instead of 220 for edition 1 cards with the purpose of adding even more value to the card if the effort was 200+

  2. IF(@D2:D303=1,@F2:F303/220,IF(@D2:D303=2,@F2:F303/150,IF(@D2:D303=3,@F2:F303/50)))

    Rough formula above was to find the card's price but it's a roundabout way of doing so. I also gave a rate of 50 to the edition 3 cards which at the moment, is not wise to do as they are new cards and their value has not been cemented into the current market so I suggested to generate a text advising to look for offers instead.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

ObiSol

New Member
Joined
Sep 11, 2021
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Formula Calculations.xlsx
ABCDEFGHI
1CodePrintTypeEd.Burn $WLEffortMinMax
2npkfv23443400301434960151
3jxfbxl7708239572372004890
45gtcb24922239063561794279
5nz9x0d427339315731663179
6k821vg4514238854443333668
7jkttbm6159239549282293362
8npw2n022233768183101641
9k8mqg41403134466992083033
10k7k6m939272472271281528
11jcxzn2172911575347852427
12n2ldnw77542482135251427
1354c8st4773219520721181426
14j42stx76112952043511426
155q2q6l40282951544551019
16n2fb0b111691223792141719
1759hpwx4944295140758918
18jsjf32223193342130717
1952cb394085293110055714
20k5sh6v9201138026501781213
21nz87zm7461294101951713
22n27sjx687324794225612
235p079j98961882271491011
24jr92dl67732383869205611
25jn9rbb69502382823220510
265csrfg52912383818334510
275ckzrc48702376770323510
28k5435j35582379762330510
29np3r1q446337018918049
305p5p95821919117155189
315cmxwm5241238167922858
32n48bc836831871599638
33jw8t5w66242946295448
3457d5bx4922218654410047
35js6x9g7633218953210547
36j2334q608521895289647
375qcbwp4514236751722636
385l4m834560217851111136
395zrtcb51452954975536
40nw4bdw11518114811978156
415n50b54973218047610636
42kprr2z9208117211769156
43k74zck36962444652836
445kgl2b4995219145611936
452qxs111495236345032136
46jtz0f2531238142922135
47nkmrxd199334210420025
48j4x6xb6679234441519535
49n03kgb79182913925135
505k0q9950592853865035
51555wnc4997236136819825
525kn1xn4290236635330124
53j63h1077612913415124
54jdqztw6709218233410524
55n3ptmb779721623337924
56j8kh3r70472953125324
57k99m4j37842473122624
585nccrz99711427702544
59jc7trw77142893044924
605zlgmd4859236330422324
61k821k044052942985824
62nj1zdf72732892834824
635sr9xj47252442832624
64nrdbch84452472692523
65n4mh3v163322651513
66wszlmk2766235026027923
67np1sdn80412942515123
685f24065309235923629023
695v5f1k4946216923310323
70nj74d349452192181313
7156zhj750962212151413
72kgw8tg44242842034613
73k8t5174151234719820612
74j3bdr5721521381968012
75jz76nx39842201961312
76bmv3mp58022431962312
775tjh1331542871915412
78jxzpdc1066011304447522
79j3hvzr4280232216817612
80j45n5m13332851675112
81np1stl247392405412
82nzhhcn17631893911612
83nkrmd3515021541568812
845ph8f347432831495012
85jsjf3h1143311713549522
86nk9hgk79002791414412
87kcfc9n94991863524822
88j13q0368932181331312
89bprs943896216612910112
90nzxvmj85012791284412
91jcs2r2185390326012
925b8ps547762421262612
93b57394985228312223412
94jx51dc77432811214512
95513lfz4892230612015612
96jp6mlx683421471188411
97nqr6dd71342391162311
98nvq81h35842821164811
99jdxprf7607229911617011
100nkrmxq1095211612888811
101b6smsz68042721154311
Sheet1
Cell Formulas
RangeFormula
H2:H101H2=IF(D2:D303=1,F2:F303/220,IF(D2:D303=2,F2:F303/150,IF(D2:D303=3,F2:F303/50)))
I2I2=IF(D2:D303=1,F2:F303/200,IF(D2:D303=2,F2:F303/100,IF(D2:D303=3,F2:F303/20)))
I3:I101I3=IF(D3:D303=1,F3:F303/200,IF(D3:D303=2,F3:F303/80,IF(D3:D303=3,F3:F303/20)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B303Cell Value>999textNO
B2:B303Cell Valuebetween 999 and 100textNO
B2:B303Cell Value<100textNO
F2:F303Cell Value>=3000textNO
F2:F303Cell Valuebetween 2999 and 2000textNO
F2:F303Cell Valuebetween 1999 and 1000textNO
F2:F303Cell Value<1000textNO
G2:G303Cell Valuebetween 350 and 500textNO
G2:G303Cell Valuebetween 349 and 200textNO
G2:G303Cell Value<200textNO
H2:K303Cell Valuebetween 5 and 1textNO
H2:K303Cell Valuebetween 6 and 10textNO
H2:K303Cell Valuebetween 11 and 15textNO
H2:K303Cell Valuebetween 16 and 20textNO
H1:K303Cell Value>20textNO
 

Forum statistics

Threads
1,148,427
Messages
5,746,618
Members
424,033
Latest member
al1en

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
Top