The following formula is not returning any result in LibreOffice Calc. In Excel 2013, the result is 33,525
Following is the formula:
IF(2500000<=B10>=1000000,1.2B27,IF(5000000<=B10>=2500000,1.3B27,IF(7500000<=B10>=5000000,1.5B27,IF(10000000<=B101>=7500000,1.5B27,IF(15000000<=B10>10000000,2,IF(20000000<=B10>=15000000,2.2B27,IF(25000000<=B10>=20000000,2.5B27,IF(30000000<=B10>=25000000,2.7B27,IF(35000000<=B10>=30000000,2.8B27,IF(40000000<=B10>=2.9B27,IF(50000000<=B10>=40000000,3B27,1)))))))))))
OK, let’s look at this formula together … Is that this piece - ...10000000<=B101>=7500000,... - was written correctly? In other parts you check the value of B10, but not of B101 Next questionable fragment - ...40000000<=B10>=2.9B27... Everywhere you compare with a constant, but here with a calculated expression…
Tell us about these two pieces, and then we will tell you how to write the “B10 between the Value and Another Value”
I think you mean something like this
=B27 * LOOKUP(B10/100000, {0; 10; 25; 50; 75; 100; 150; 200; 250; 300; 350; 400; 500.00001}, {0; 1.2; 1.3; 1.5; 1.7; 2; 2.2; 2.5; 2.7; 2.8; 2.9; 3; 0})
Also, a very similar result you can get with this formula
=B27*ROUND(1,111504005822E-023*B10^3-1,81127850644E-015*B10^2+0,000000102675054609*B10+1,066893558;1)Sorry! The formula is not acceptable syntactically. In addition it cannot be expected to produce something reasonable, supposed some syntactical rectification is made. Then, you tell us:
The following formula is not returning any result in LibreOffice Calc.
Of course the formula must be recognised as one. Therefore it has to begin with = This assured it will return something in any case. Even if the formula is syntactically unacceptable you will get an alert explaining this. Otherwise, in general, the result may be number, text or error value by type. Do not tell us what did not happen (as compared with your expectations) but what actually happened, and what you expected in addition, if applicable.
Reduce your examples to be relevant but simple, please. More than two nested calls of IF are mostly bad in the first place. They are surely not needed to give an example of your issue.
Let me focus now on the very first condition in your example reading 2500000<=B10>=1000000:
2500000<=B10 which may result in FALSE numerically equivalent to 0 or TRUE numerically equivalent to 1. The result will then be compared by >= with 1000000. Since 0 and 1 both are less than 1000000 the condition will always be FALSE.And so on.
What do you expext 2.9B27 to be taken for? It should not be an acceptable expression in any spreadsheet.
IMHO 2.9B27 meant 2.9 * B27. And fourth 1.5B27 meant 1.7 * B27 or 1.8 * B27
@JohnSUN : I guessed similarly, but do not understand your remark
And fourth 1.5B27 meant 1.7 * B27 or 1.8 * B27.
The subexpressions mentioned should not be accepted by Excel, as the OP claimed, anyway.
random32 has written 1.5B27 twice - on [5000000…7500000] and on [7500000…10000000]. I think it’s just a typo