You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Formulas that use the rules of a cell's data format... can it be done?

So I've exported my accounts for the year from my Squarespace store and I need to get the figures for sales without taxes. Sadly Squarespace doesn't allow for multiple levels of tax on products (we have 20% and 0%) so I need to input them manually for each product sold and get the total. Which is a bit of a pain but not the end of the world.


The problem is if I have a cell formatted as a currency (£) and I use a formula to subtract the cell formatted as a percentage (%) it's giving me an answer that's subtracting the figure as a whole number not as a percentage.


Here's screenshot showing what I mean. The sum should be...


£3.60 - 20% = £2.88


But it's doing...


£3.60 - 20 = £3.40



Even doing a sum that's -20% instead of the % cell doesn't work...



Is there a way around this apart from do a sum that's x0.8 on each line that needs it as there's 1000's of them and I need to pick out each which ones are each rate which will take far too long to do.


It seems to me to be a pretty straight forward thing to ask a spreadsheet to do but Numbers disagrees!


Any help would be much appreciated.

Posted on Mar 5, 2021 4:24 AM

Reply

Similar questions

4 replies

Mar 5, 2021 10:25 AM in response to Slumberjack

"Can't understand why Numbers can't just do it with some built in coding though."


Actually, it does 'just work,' but you do have to tell it what to do.


"20%" is the number 0.20 expressed as a percent.


Your first formula, I2 - J2 tells Numbers to get the number in cell I2 (3.60) and subtract the number in J2 (0.20)

Numbers follows those instructions and returns the correct answer: 3.60 - 0.20 = 3.40


Ian's modified version tellls Numbers to get the number in cell I2 (3.60) and subtract 20% of that number (0.20X3.60).

Numbers follows those instructions and returns the correct answer: 3.60 - (0.20X3.60) = 2.88


In both cases, Numbers 'just works' and delivers the correct answer to the question asked.


Regards,

Barry







Formulas that use the rules of a cell's data format... can it be done?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.