The specified formula cannot be entered because it uses more levels of nesting than allowed in the current file format

farazaidi

New Member
Joined
Jun 12, 2018
Messages
4
=IF(E7="Bostan,MA",IF(M7<100,"8.84","5.84")*M7-P7,IF(E7="Delhi",IF(M7<45,"6.55",IF(M7<500,"1.50","0.30")*M7-P7),IF(E7="Budapest",IF(M7<100,"4.90","3.18")*M7-P7,IF(E7="Budapest",IF(M7<100,"4.90","3.18")*M7-P7,IF(E7="Oradea",IF(M7<100,"5.29",IF(M7<250,"4.56",IF(M7<1000,"4.31","0.30")*M7-P7,IF(E7="Guangzhou",IF(M7<45,"10.97", IF(M7<100,"4.64",IF(M7<250,"3.37",IF(M7<500,"3.06",IF(M7<1000,"2.74","2.42")*M7-p7))))))))))))

how to solve if
 
A few comments.

1. Your original formula is a bit hard to follow anyway because it is not a valid formula - there are misplaced & unmatched parentheses at least.
2. When using numerical data, there is no need, and better not to, enclose them in quote marks. So instead of IF(M7<100,"8.84","5.84") just use IF(M7<100,8.84,5.84)
3. You appear to have two identical sections for Budapest in the formula. I can't see why you would have that. :confused:

Anyway, see if you could use something like this. In the lookup table in column T onwards
- List the cities down the column
- The green section lists the change-over points, starting at 0 (assuming column M will house positive numbers) for each city.
- The yellow section is the corresponding multiplication value for each of the change-over points for that city.

Then the formula to replace your original long one is shown in A7, and copied down.

Excel Workbook
AEMPQTUVWXYZAAABACADAEAF
1
2Boston, MA01008.845.84
3Delhi0455006.551.50.3
4Budapest01004.93.18
5Oradea010025010005.294.564.310.3
6Guangzhou045100250500100010.974.643.373.062.742.42
7402Boston, MA5040
8560Oradea12510
9250Oradea110080
Look up city factor
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top