PDA

View Full Version : Solved: Multiple formulas in a single cell...



JohnyG
09-04-2008, 10:11 PM
I have a worksheet contains 4 columns. Please find attached the sample output sheet for the same.

Need to develop some calculation logic.Logic goes like :

Value of Column D is calculated basis two formulas.

Formula 1: Due Date - Contact Date.(calculate only working days)
Now if the value of Column D value (Lead Time) is >= 7 and value of Column 1(Request Type) is "RFI" then Column D cell background color changed to green else red.

Similarly if the value of COlumn D (Lead Time) is >= 14 and value of Column 1(Request Type) is "RFP" then COlumn D cell background color changed to green else red.

If Column A contains anyother value apart from "RFI" or "RFI" then no output is required in COlumn D.

Please help

Regards,

Krishna Kumar
09-05-2008, 01:46 AM
Hi,

In D3 and copied down,

=IF(B3<>"",(C3-B3)*5/7,"")

Select D3: D12

In COnditional Formatting,

Condition 1 Formula Is:

=OR(AND(LEN($D3)>0,$A3="RFI",$D3>=7),AND(LEN($D3)>0,$A3="RFP",$D3>=14))

Condition 2 Formula Is:

=OR(LEN($D3)>0,AND($A3="RFI",$D3<7),AND(LEN($D3)>0,$A3="RFP",$D3<14))

HTH

Bob Phillips
09-05-2008, 01:58 AM
In D3 and copied down,

=IF(B3<>"",(C3-B3)*5/7,"")

Surely, better to use NETWORKDAYS?

JohnyG
09-05-2008, 02:52 AM
Thanks for ur help Krishna.. However, I am not getting the correct output. Please find attached a file that contains two sheets "Solution" and "Desired Output"

Solution --> Contains the output that I got after putting ur formulas.
Desired Output --> Contains the desired output.

Please help

Krishna Kumar
09-05-2008, 02:52 AM
Surely, better to use NETWORKDAYS?

Exactly.

JohnyG
09-05-2008, 02:54 AM
Thanks for ur help Krishna.. However, I am not getting the correct output. Please find attached a file that contains two sheets "Solution" and "Desired Output"

Solution --> Contains the output that I got after putting ur formulas.
Desired Output --> Contains the desired output.

Please help

Krishna Kumar
09-05-2008, 03:03 AM
As xld suggested you could use NETWORKDAYS function.

Invoke Analysis Toolpak via Tools>Add-In

In D2 and copied down,

=IF(B2<>"",NETWORKDAYS(B2,C2),"")

In CF

1.
=OR(AND(LEN($D2)>0,$A2="RFI",$D2>=7),AND(LEN($D2)>0,$A2="RFP",$D2>=14))

2.
=OR(LEN($D2)>0,AND($A2="RFI",$D2<7),AND(LEN($D2)>0,$A2="RFP",$D2<14))

HTH

JohnyG
09-05-2008, 03:31 AM
Thanks Krishna .. for ur help .. :clap:


Regards