Forum Discussion
Help with creating an "IF" formula
I need help in creating an IF formula. I am trying to create a formula that will populate the cell with the amount of Trip Pay is due to a traveler based on the number of miles that is entered in the miles column. So Cell A1 will be the miles Column and A2 will be the formula. I need it to calculate based on the following information. The letter m bellow = miles
If the miles entered in A1 is between 26-50 miles then the calculation would be m*0.45+77.50
If the miles entered in A1 is between 51-75 miles then the calculation would be m*0.45+89.25
If the miles entered in A1 is between 76-100 miles then the calculation would be m*0.45+101
If the miles entered in A1 is <101 miles then the calculation would be m*0.45+54
I can't figure out how to tell Excel to calculate based on a number range (i.e. <26 and >50, or if between 51&75).
If you have any ideas, please let me know.
Thanks.
6 Replies
- bonjour! je m'excuse de vous déranger. je suis un nouveau utilisateur du compte Microsoft et je ne parviens pas a l'interface a partir de laquelle je peux poser les problèmes que je rencontre présentement sur excel. veuillez s'il vous plait m'indiquer le chemin à suivre pour que je puisse poser mon problèmes a la communauté. merci
Hi Norman,
Actually your IFs give the answer if convert to nested IF. The only, the logic is bit unclear
- what shall be if A1 is less than 26;
- last "if" is in conflict with previous ones, all of them are for A1 < 101. Perhaps >101?
Formula looks like
=A1*0.45+IF(A1<26,0,IF(A1<51,77.5,IF(A1<76,89.25,IF(A1<101,101,54))))
Thanks for your help. To answer your question, there would never be a time that the number in A1 would be less than 26.
So we may keep zero for that
