IF formula that can take account of rows order

[George_1990]

Hello guys,

Can you please help me with the following issue:
I have 2 item components for which i want to calculate the difference between a 1 time payment vs several installments option. The two components however are not consecutively distributed therefore sometimes the formula has to consider bottom row, sometimes the row above.
Example:
NR. time P1 P2
310 1 0 420 - here I can put an IF P1=0, then calculate 420 - (10*14) - ok
310 10 14 0 - here the else of the above IF would give result "0" - ok

163 20 20 0 - but here I cannot use the above formula because the cell are not in the same order.
163 1 0 590

315 17 14 0
315 1 0 420

939 1 0 425
939 14 14.5 0


Can you please help me with a smart formula that can go through the list and retireve the relevant result?

Thank you very much!

George

[Glenn Kennedy]

It is impossible to tell what is in which column....

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[George_1990]

Hi,
Here is a sample file.

Thanks for your help

I will change the thread name right awa
Thank you

[Glenn Kennedy]

If this is NOT what you want, please tell us what ALL your expected answers are:

=IF(C2=0,B3*C3,IF(C2<>0,0,B1*C1))