How to convert excel formula to numbers?

Question

Level 1

0 points

How to convert excel formula to numbers?

Trying to get a formula set up in Numbers ... not having much luck! 😟

Column B is the item's price, column D is the discounted price. To determine the discounted price, I want to be able to enter the items price in column B, then a code in column C. Codes are as follows:

H = 50% off

Y = 30 % off

J = 40 % off

T= 20% off

F = 75% off

When I enter the code, I want it to be able to calculate the discounted price in column D.

I am more familiar with Excel but trying to help a friend get this set up in numbers. I believe the excel formula would be as follows??

=IF(OR(C="T",C="t"),B*(100-H$2)/100, IF(OR(C="Y", C="y"), B*(100-H$3)/100,IF(OR(C="J",C="j"),B*(100-H$4)/100, IF(OR(C="H",C="h"), B*(100-H$5)/100, IF(OR(C="F", C="f"),B*(100-H$6)/100,0)))))

Any help would be GREATLY appreciated!

Thanks!

MacBook Pro

Posted on Jun 27, 2012 6:31 PM

Reply

Answer 1

Top-ranking reply

Wayne Contello

Level 7

26,583 points

Jun 27, 2012 6:42 PM in response to calliegirl22

I suggest using a sperate table to contain the discount codes and the corresponding discount (I named it "Discount Codes"):

Then create a popup menu for column C with the discount codes using the cells inspector:

I added a "None" discount code in the pop up.

In the Data table the formula for column D (the discounted value is):

D2=IFERROR(VLOOKUP(C2, Discount Codes :: A:B, 2, 0), 100)*B2/100

select D2 and fill down

Reply

Answer 2

calliegirl22 Author

Level 1

0 points

Jun 27, 2012 8:17 PM in response to Wayne Contello

Wayne, thank you SO MUCH for helping!

I have everything set up like you showed (I think), but it looks like the discounted price column is just pulling over the amount under the discount % column. In other words if the item price is $100 and I pick the discount code "Y" it needs to discount the $100 by 30%, that should make the new discounted price $70 (in column D), but it is showing $30 instead. What am I missing? Have I messed this up somewhere?

Thanks again for your help!

Reply

Answer 3

Badunit

Level 7

28,414 points

Jun 27, 2012 8:23 PM in response to calliegirl22

The formula is a little off so that a 30% discount meant 30% of the price, not 30% off . I think this is it but I haven't tested it:

D2=(1-IFERROR(VLOOKUP(C2, Discount Codes :: A:B, 2, 0), 0))*B2

Reply

Answer 4

Barry

Level 9

59,951 points

Jun 27, 2012 10:50 PM in response to calliegirl22

Hi Callie,

Here's another approach.

Like Wayne, I've added a Zero code (Z) to allow a non discounted price (line 7), and recommend using a pop-up menu for the entries in column C.

Column D contains the formula below, which used FIND and CHOOSE to translate the discount code into a numeric value, then uses that value to calculate the discounted price:

D2, and filled down: =B-B*CHOOSE(FIND(C,"HYJTFZ"),0.5,0.4,0.3,0.2,0.75,0)

While this was an interesting exercise, and does give you a single-table solution, my own preference would be a solution similar to Wayne's (with the formula correction supplied by Badunit). Putting the codes and related discounts into a separate table allows for easier editing, should you ever want to change the discount rate indicated by each code, or to add or remove codes.

Regards,

Barry

Reply