Forum Discussion
IFS Formula is giving me fits
Hello, I am trying to build a proposal sheet for my new business. I've created a dropdown for the service, and the rates are dependent upon the service, which is the reason for the IFS statements. I've broken the services up into areas, though some are fairly lengthy. The issue I am now having is that the following formula, when pasted into the cell, is simply showing as text and not as a formula and I am not getting an error message. The cell is formatted as General, there are no extraneous apostrophes or spaces, but I can't get it to go. Can you help?
=IFS(P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 10 min 901-1,800 words”,”$590.63”,P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 120 min 15,001-20,000 words”,”$2,187.50”,P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 30 min 1,801-5,500 words”,”$853.13”,P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 5 min 0-900 words”,”$350.00”,P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 60 min 5,501-9,500 words”,”$1,312.50”,P11=”DRTV_Long Form TV or Radio Broadcast Documentary - up to 90 min 9,501-15,000 words”,”$1,750.00”)
Mac or not, in Excel are to be used straight quotation marks, not curve ones. Even if on Mac they are default for text editing. In addition, numbers are to be returned as numbers, not as texts. Plus it's better to add condition if no one from previous is met. Finally
=IFS(P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 10 min 901-1,800 words",590.63, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 120 min 15,001-20,000 words",2187.5, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 30 min 1,801-5,500 words",853.13, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 5 min 0-900 words",350, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 60 min 5,501-9,500 words",1312.5, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 90 min 9,501-15,000 words",1750, TRUE,0 )
4 Replies
pxg615 you have the wrong character for quotation marks. You need to use the double quotes to the left of the enter key (shift-apostrophe). Easiest way to correct the formula will be to select the cell (P11), go to find and select (ctrl+h), replace this character -> ” with this character -> ".
Mac or not, in Excel are to be used straight quotation marks, not curve ones. Even if on Mac they are default for text editing. In addition, numbers are to be returned as numbers, not as texts. Plus it's better to add condition if no one from previous is met. Finally
=IFS(P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 10 min 901-1,800 words",590.63, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 120 min 15,001-20,000 words",2187.5, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 30 min 1,801-5,500 words",853.13, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 5 min 0-900 words",350, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 60 min 5,501-9,500 words",1312.5, P11="DRTV_Long Form TV or Radio Broadcast Documentary - up to 90 min 9,501-15,000 words",1750, TRUE,0 )
