Hello, could someone tell me why I get a syntax error with this formula:
(IF(ISBLANK('Actual - June' Food),"",Budget Food−'Actual - June' Food)
I'm trying to get Numbers not to calculate formula if cell is blank, but i get this syntax error:
The operator “+” expects a number, date, or duration but found “.”
iMac 21.5″, macOS 12.7
If 'Actual - June' Food is blank, the result of your IF formula is the null string "". You cannot use the math operators +-*/ on a string unless that string is a number that happens to be formatted as text.
Given that this is a simple math problem, set the IF results to be 0 instead of "".
But, really, you can make the entire formula much much simpler. It looks like you want to add up (Budget - Actual) for each month, but not including the months that are empty/blank. Using your screenshot to determine the column-row cell references, I think this formula will do what you want:
=B5×COUNT(C5:N5)−SUM(C5:N5)
B5 is your Budget number for food and C5:N5 is Jan-Dec actuals if I got this right.
COUNT gives a count of the monthly cells that have numbers in them. Multiply that by the budget.
SUM gives the sum of the monthly cells.
If you want to see column-row addressing in your formulas, go into Numbers->Preferences and uncheck "Use header names as labels".
If 'Actual - June' Food is blank, the result of your IF formula is the null string "". You cannot use the math operators +-*/ on a string unless that string is a number that happens to be formatted as text.
Given that this is a simple math problem, set the IF results to be 0 instead of "".
But, really, you can make the entire formula much much simpler. It looks like you want to add up (Budget - Actual) for each month, but not including the months that are empty/blank. Using your screenshot to determine the column-row cell references, I think this formula will do what you want:
=B5×COUNT(C5:N5)−SUM(C5:N5)
B5 is your Budget number for food and C5:N5 is Jan-Dec actuals if I got this right.
COUNT gives a count of the monthly cells that have numbers in them. Multiply that by the budget.
SUM gives the sum of the monthly cells.
If you want to see column-row addressing in your formulas, go into Numbers->Preferences and uncheck "Use header names as labels".
I am assuming the error message was about "-" expects a number because there is no + in your formula.
If 'Actual - June' Food has a formula in it, that cell is not blank even if the result of the formula is "". Instead of ISBLANK just do a straight up comparison. I am, of course, assuming this is what is going on, based on the error message you got.
=IF('Actual - June' Food = "", "", Budget Food−'Actual - June' Food)
That works!!! Thanks so much! The only thing I noticed was that I cannot leave a cell blank; instead I have to put in a zero for the month if I did not spend anything in order for it to calculate correctly, which is not a problem!
Impossible to tell without seeing more of the data.
The formula itself looks OK (assuming you have a table with columns labelled 'Budget' and 'Actual - June', and a row called 'Food')
So the question is what values are in those related fields? I can replicate a similar error if either of the related fields contain text (although I'm not seeing where the '+' in the error message comes from).
So I apologize for not giving a thorough explanation as I am a newbie to Numbers! Here's what the whole formula actually is: (Budget Food−'Actual- January' Food)+(Budget Food−'Actual - February' Food)+(Budget Food−'Actual - March' Food)+(Budget Food−'Actual - April' Food)+(Budget Food−'Actual - May' Food)+ (IF(ISBLANK('Actual - June' Food),"",Budget Food−'Actual - June' Food) +(Budget Food−'Actual - July' Food)+(Budget Food−'Actual - August' Food)+(Budget Food−'Actual - September' Food)+(Budget Food−'Actual - October' Food)+(Budget Food−'Actual - November' Food)+(Budget Food−'Actual - December' Food)) (See Below).
So for every category in my budget, I would like to keep a running total of what the amount is in that category, be it either positive or negative. I have a row for every category and what the budget allowance is. Then I have a final column at the right that should be the only one that has a formula in it.
The formula above is in the final column and it worked well until I tried to tell Numbers to not calculate any blank cells, (I was practicing this for the month of June). In this example I left the entire formula in but I added what I thought would tell Numbers to not calculate blanks.
This is the error it keeps returning:
The operator “+” expects a number, date, or duration but found “.”
Also, how do you know if a blank cell is really empty?
Thanks so much for answering, and I hope I've explained it well enough this time!
Barb
So I apologize for not giving a thorough explanation as I am a newbie to Numbers! Here's what the whole formula actually is: (Budget Food−'Actual- January' Food)+(Budget Food−'Actual - February' Food)+(Budget Food−'Actual - March' Food)+(Budget Food−'Actual - April' Food)+(Budget Food−'Actual - May' Food)+ (IF(ISBLANK('Actual - June' Food),"",Budget Food−'Actual - June' Food) +(Budget Food−'Actual - July' Food)+(Budget Food−'Actual - August' Food)+(Budget Food−'Actual - September' Food)+(Budget Food−'Actual - October' Food)+(Budget Food−'Actual - November' Food)+(Budget Food−'Actual - December' Food)) (See Below).
So for every category in my budget, I would like to keep a running total of what the amount is in that category, be it either positive or negative. I have a row for every category and what the budget allowance is. Then I have a final column at the right that should be the only one that has a formula in it.
The formula above is in the final column and it worked well until I tried to tell Numbers to not calculate any blank cells, (I was practicing this on the month of June). In this example I left the entire formula in but I added what I thought would tell Numbers to not calculate blanks.
This is the error it keeps returning:
The operator “+” expects a number, date, or duration but found “.”
Also, how do you know if a blank cell is really empty?
Thanks so much for answering, and I hope I've explained it well enough this time!
Barb
A blank cell means you do not yet have data for that month, which is why you don't want to include the blank ones in the result. So, if you mean you need to put a zero in a month that has gone by when you spent zero that month then, yes, that makes perfect sense.
Syntax error in Numbers
