Can Numbers be programmed to determine between a percentage and a plain number?

Question

Level 1

4 points

Can Numbers be programmed to determine between a percentage and a plain number?

I have an invoicing spreadsheet I work with. Sometimes price is determined by a percentage over cost, sometimes it's just a straight number. I have the following setup:

Our cost	Percentage	Cost to customer
number	percentage	Our cost x percentage
100	15%	115
200	225	225

I would like the 'cost to customer' column to recognize whether the number in the percentage column is actually a percentage or if it's a number without a % symbol in that cell, so when I don't want to calculate cost by percentage, I can just enter a straight number there and have that amount entered in the 'cost to customer' cell (as illustrated above). Is that possible? Thanks for your help!

Tony

iWork Numbers-OTHER

Posted on Dec 19, 2013 11:20 AM

Reply

Answer 1

Dec 19, 2013 11:52 AM in response to shineykid

Tony,

The answer depends on whether you plan to reuse the table. Once you have overwritten the expression that calculates a markup, you can't go back and use the automatic calculation equation in that same row because it's been erased by typing over it.

Jerry

Reply

Answer 2

Badunit

Level 7

25,784 points

Dec 19, 2013 12:00 PM in response to shineykid

=IF(RIGHT(B3,1)="%",A3*(1+B3),B3)

Reply

Answer 3

Dec 19, 2013 12:02 PM in response to Badunit

Badunit,

You got it. I misread the problem.

Jerry

Reply

Answer 4

Dec 19, 2013 12:15 PM in response to shineykid

wouldn't it be more straightforward (in my humble opinion) to be consistent with the use of the cell?

If you do not want to inflate (or deflate) a cost by a percent just enter the percent in the "Percentage" column and leave the "Fixed Price" column empty. If you want to override the percentage enter a value in the "Fixed Price" column.

D3=IF(ISBLANK(C3), A3×(1+B3÷100), C3)

Reply

Answer 5

shineykid Author

Level 1

4 points

Dec 19, 2013 12:35 PM in response to Wayne Contello

Yes Wayne, I think your way is better. Thanks to badunit and Jerrold for all the help!

Reply

Answer 6

shineykid Author

Level 1

4 points

Dec 19, 2013 1:37 PM in response to Wayne Contello

Hi Wayne, I think I'm entering something wrong - your formula works if I have something in the Fixed Price column, but not if it's empty. My columns are called Cost, Percentage, Fixed Price, and Price (Price being the cell that the formula is being entered in). What's wrong with this?

=IF(ISBLANK(Fixed Price), Cost*(1+Percentage,÷100),Fixed Price)

I get an error message saying "this formula contains a list where a single argument is expected." Any ideas?

Thanks again!

Reply

Answer 7

Dec 19, 2013 1:37 PM in response to shineykid

A comma in your formula is not correct and sould be removed:

"ntage,÷1"

The correct formula is:

=IF(ISBLANK(Fixed Price), Cost*(1+Percentage÷100),Fixed Price)

Reply

Answer 8

shineykid Author

Level 1

4 points

Dec 19, 2013 1:50 PM in response to Wayne Contello

Thanks again, worked great!

Reply