Can excel do this ? ( Help please !)

[Jock1970]

I have a fairly large spreadsheet which requires an extra column added which is to have values taken from existing cells within the worksheet. I want the new value to come from different columns in each row dependant upon values in that row.
If any one can help I can e-mail a small sample which has an explanation of what I am trying to achieve.

Otherwise, here is what I want described in as few words as possible :-
My new column will be headed 'Projected'.
If the cell in the same row under a 'Status' column has a 'C' in it then the value in 'Projected' should be 0 (zero).
If 'Status' is not 'C' then the 'Projected' value should be taken from the 'Final' column, unless this is empty (null?) then it should be the greater of either (1) a column named 'Payment' or (2) the sum of 2 columns ('Value' & 'Variation'.
If 'Value', 'Variation' & 'Payment' are all empty (Null ?) then 'Projected' should then take the value from another field called 'Estimate'. Phew !

Hope someone understands the above,
Thanks for reading, hope you can help,

Regards,

Jock

 

[xlbo]

No worries:
if('Status'=&quot;C&quot;,0,if('final'<>&quot;&quot;,'final',if(and('Value'=&quot;&quot;,'Variation'=&quot;&quot;,'Payment'=&quot;&quot,'Estimate',if('Payment'>'Value'+'Variation','Payment','Value'+'Variation'))))

swap the field names in ' ' for the cell references then copy the formula down

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[fredericofonseca]

Yes it can be done.
Not going to do it all..

let
status=a1
projected=b1
final=c1
payment=d1
value=e1
variation=f1
estimate=g1

if(a1=&quot;C&quot;,0,if(c1<>&quot;&quot;,c1,if(d1+e1+f1)>0,max(d1,(e1+f1),g1)))


If not complete/correct at least you have an idea.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jock1970]

Thanks guys !
I used your formula xlbo as you replied first. It worked perfectly in the sample spreadsheet which I made up but has a problem when I use it in the proper large worksheet. I know what is causing the problem but not how to alter your formula to get round it - can you advise please ?
The problem is that in any rows where payment, value and variation are all empty the projected column is shown at 0 (zero)(it may actually be empty (null) but the column is formatted as currency so shows a '£' sign and a dash). I didn't mention earlier but the payment cell is actually a total of 3 other cells (has formula &quot;=M9+O9+Q9&quot; in it) - if I remove the formula from this cell, your formula then works perfectly.
Can you help,

Thanks,

Jock

 

[fredericofonseca]

Mine should avoid that particular problem. Give it a try.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jock1970]

I've tried yours now Frederico and Excel won't accept the formula, which I have added my cell references to, and now looks like :-
=if(H7=&quot;C&quot;,0,if(W7<>&quot;&quot;,W7,if(S7+K7+L7)>0,max(S7,(K7+L7),G7)))
It says there is an error in it and highlights the first L7 in the formula box when I close the error message.
I can't see what's wrong with it !
Can you ?

Thanks for your help so far,

Jock

 

[xlbo]

just change =&quot;&quot; to =0
should work

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[fredericofonseca]

The brackets weren't paired correctly. (I did not try it).
I think now they are correct.

=IF(H7=&quot;C&quot;,0,IF(W7<>&quot;&quot;,W7,IF((S7+K7+L7)>0,MAX(S7,(K7+L7)),G7)))




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jock1970]

Thanks a lot guys. Both your formulas appear to work fine now (i'm using yours though xlbo 'cos you replied with the fix first.
Cheers,

Jock

 

[xlbo]

Have to say that if it works, I'd use Jock1970's as it is smaller and therefore more efficient.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244