Nested calculations following a ROUND statement.

[Altaire]

I'm new to this forum and this is my first post. I hope I picked the correct sub-forum. Please feel free to correct me if I am not doing this correctly.

I recently received a sample Excel spreadsheet where prices are determined in 3 levels (Max, Mid and Min) where 2 of the prices are calculated based on a calculation derived from the 3rd value. At least I think this is the case since it appears that there are different formulas depending on which of the 3 prices is used as the starting point.

Here is an example of a formula that puts the value 3.99 in cell D86. When I select that cell (D86), this is the formula I see:

=IF(D86<2.99,D86,ROUND(D86*0.5,0)-0.03)

Can anyone tell me what this means in English?

Thanks,
Altaire

[Sam Capricci]

if D86 is less than 2.99, return D86, otherwise take D86, multiply it by 0.5 (in otherwords take half of it) then round that to the nearest whole number and finally subtract 0.03 from that number.
you can see it if you take it apart in the cell, put 2 in D86 with just =IF(D86<2.99,D86,"") and it will return 2, if you put 3 in it it will return a blank.
do the same with the round part without the -0.03 then with the -0.03.

[Altaire]

Thanks, Sambo kid: I just tried it. How cool is that! I tried your suggestion and even checked a number of other cells. I now see what the person who built this spreadsheet is trying to accomplish. Now I just need to write the code to make this process work in a web app.

[FlameRetired]

Withdrawn by FR.

[Altaire]

Thanks, Dave.

I appreciate your help. Also by changing it for me, you provide a good example of how it should have been written.
I will go through the steps you noted so I know how to make changes AFTER I have posted something. Hopefully, that won't be necessary.

Altaire

[Altaire]

I have updated the title at the request of the moderator although I'm still not sure if this is specific enough for a title. Any comments or suggestions would be appreciated.