Here is an Excel Formula for creating an Excel Form which will apply separate Math Formulas depending on the input, and if no input is entered a default text will be displayed requesting input.
In this case, we’ll apply one mathematical formula if the form is filled out by a Male, and a Separate mathematical formula if the Form is filled out by a Female.
The formula will check for the Presence of “M” or “F” in the form’s Gender Box, or lack or presence. The M & F are not Case-Sensitive
This Formula has the following attributes:
- IF the letter M is found, THEN Math Formula 1 is applied.
- IF the letter F is not found, THEN Math Formula 2 is applied.
- IF no input is found, THEN the Formula will simply display: (Input Gender as M or F) in the Result Box
- This formula utilizes several other inputs from other user-entered fields
Here is an example of the formula, where [cell#] are the cells containing other information that has already been input by the user.
=IF([cell1]=”M”,(134*[cell2])+(9.5*[cell3])-(8*[cell4])+73,IF([cell1]=”F”,(154*[cell2])+(12.75*[cell3])-(3*[cell4])-282,”(Please Input Gender as M or F)”))
The breakdown is =IF [cell1] has an M, then the equation (134*n1)+(9.5*n2)-(8*n3)+73 is applied to the Result Box using the information in the corresponding cells; IF [cell1] has an F, then the equation (154*n1)+(12.75*n2)-(3*n3)-282 is applied to the Result Box in the same manner; and IF No M or F is Found, the “IF NOT” is applied as a default and the Result Box will display: (Please Input Gender as M or F)
IF ISBLANK Function for Excel Formula to Apply Mathematical Equation Only if Value is Present.
In this scenario, an Equation will be run only if text is entered into the Box, and if nothing is entered, there will be a text showing “(Please Complete Form)”.
=IF(ISBLANK([cell1),”(Please Complete Form)”,(([cell2]*114)/[cell3])/[cell3])
This says that if cell1 is blank, a text will read (Please Complete Form), and if data is entered, the formula (cell2 x 114) ÷ cell3 ÷ cell3
Also, you can use IFERROR after the Equal sign in the formula to turn any error codes into Zero. You’ll have to enclose the equation in parentheses, and add ,0 to the end of the equation, but inside the close parenthesis. For example the formula =L22/O78 would look like =IFERROR(L22/O41,0)
Hiding Formulas in Other Cells until Input is Present in a Cell:
Additionally, if you have a cell which may not throw an error, but give a strange calculation due to missing information, you can fix that as well. For example, if you have put the following formula in Cell B3: =PRODUCT(F8,D12), where F8 may contain a fixed number, such as 10, and D12 varies depending on user input, and if there is no input in Cell D12, then the number 10 simply appears in Cell B3, which may look terrible on your sheet. With more complex formulas you may end up with a bunch of strange negative numbers populating your sheet.
You can type the following in Cell B3 between the “=” sign and your formula, followed by a close parenthesis: IF(ISBLANK(D12),”0″,
So, it looks like this:
=IF(ISBLANK(D12),”0″, [your original formula])
where D12 is the cell needing input, which is often blank, etc
so the entire formula in cell B3 would look like this:
or the formula =N1+K8+P3 would look like =IF(ISBLANK(F8),”0″,N1+K8+P3)
you could also change the “0” to whatever you want displayed, such as “unknown”
If you were to use this with the first example where we used M & F gender selection, just add it to the beginning after the “=” and add a close parenthesis to the very end, such as:
=IF(ISBLANK([cell9]),”0″,IF([cell1]=”M”,(134*[cell2])+(9.5*[cell3])-(8*[cell4])+73,IF([cell1]=”F”,(154*[cell2])+(12.75*[cell3])-(3*[cell4])-282,”Input Gender as M or F”)))
Where [cell9] is the Cell where the user inputs their height, if there is no M or F in the Gender Cell, It will display “Input Gender as M or F” or whatever you put between the parentheses, and if no height information has been input, it will just read 0, even if the Gender has been filled in, rather than try and make some strange calculation.
Monitoring Multiple Blank Cells:
If you want a cell to wait until 2 or more cells are filled with data before completing a calculation, and until then just display 0 or some other message, use OR, such as:
If your original formula is simply [cell6]*[cell7], putting IF(ISBLANK([cell1]),”0″, will cause an error or strange calculation if only [cell1] is filled in but [cell2] is left blank, so use the following:
Now if either cell1 or cell2 are blank, you’ll still get a 0 or whatever you put between the parentheses.
You probably can’t copy & paste directly from this blog to excel without getting the #name? error in the cell, and that’s because the type of the quotation marks is different in this blog, so just type your own quotation marks, etc