How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Computer Support Specialist
Category: Computer
Satisfied Customers: 40345
Experience:  Over 15 years of consulting to the IT industry
32989067
Type Your Computer Question Here...
Richard is online now

need a formula for exel If J=Y and IFD=0 then N=C can

Customer Question

need a formula for exel
If J=Y
and IFD=0
then N=C
can you help please
Submitted: 3 years ago.
Category: Computer
Expert:  Richard replied 3 years ago.
Thank you for your question, my name is XXXXX XXXXX I look forward to assisting you.

Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.

This is the formula to use

=if(and(J1="Y",IFD1=)),"C","")

You need to specify a Cell number as well so I added this as 1, and you cannot assign a value to another Cell via a formula, so you put the formula I gave you in N.

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

it does not seem to work


I need value n =value c that is 10.7


can you help thanks

Expert:  Richard replied 3 years ago.
ok, if you want the formula to be if N = C then 10.7

Then it is

if(N1=C1,10.7,"")

Is this what you want?

If not, can I see your sheet please? You can upload it by doing the following.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.

sorry no


if J =Yand if d= o


then n = c


trying this on line 141


thank you

Expert:  Richard replied 3 years ago.
Are you doing this as a formula or via VBA Code Roger?

And what are the actual Cell references? eg: N1, J1 ect.

Customer: replied 3 years ago.

as formula


cell reference n141,J141,etc

Expert:  Richard replied 3 years ago.
ok, then to achieve

if J =Yand if d= o

then n = c


You use in N141

=if(and(J141=Y141,D141-0),C141,"")

That works for the rules you set.

If you want, give me the file and I can do this directly for you Roger.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 3 years ago.


=if(and(J141=Y141,D141-0),C141,"")


please see underline above as this does not give the correct answer


thank you

Expert:  Richard replied 3 years ago.
You say

if J =Y and if d= o

then n = c

So you now do not want the condition to be If J = Y?

And it is not

D141-0

It is

D141=0

That was a typo on my behalf.

Customer: replied 3 years ago.

hi


yes I need IF J=Y as sometimes it is not


I will upload the whole sheet if it is not too big

Expert:  Richard replied 3 years ago.
ok, then my formula works.

Look at what you underlined

=if(and(J141=Y141,D141-0),C141,"")

So if J141 = Y141

Please upload the sheet, it will be easier for me to do it directly on it.
Customer: replied 3 years ago.

have uploaded the sheet

Expert:  Richard replied 3 years ago.
In need the file ID roger so I can download it
Customer: replied 3 years ago.

have uploaded the file twice called march2013


have you got it


thank you roger

Expert:  Richard replied 3 years ago.
Roger,

May I ask you follow all my instructions and read the messages I send you?

I need the FILE ID TO DOWNLOAD IT

Customer: replied 3 years ago.

have uploaded three times and got this


 


Mar2013.xls




  • File ID: 345610

  • File size: 267 KB

  • Time to live: 7 days



Download link:



Forum link:







Properties







Friendly file name:



Time to live:

days (1-90)


Description:



Protect file with password


Set a password:



Retype password:






Expert:  Richard replied 3 years ago.
Thank you for the File ID

I have done this, the formula is in Column N

Go to www.wikisend.com and download File ID 615940

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

hi thanks got file back


sorry it is not correct yet


maybe I am unable to explain myself


 


line 141 should have10.7 in N as there is a Y in J


line 481 should have a blank as there is no y in J


 


 

Expert:  Richard replied 3 years ago.
ok, so if it equals the letter Y, and not the Column Y

I changed this for you and you can download it from www.wiksiend.com under File ID 432356

Let me know the results please
Customer: replied 3 years ago.

hi


thanks that works


now in the same spread sheet lone 573 M should be sum of d so formula shoud be =IF(AND(J573="y",D573>=0),D573,"") plus K= Y


hope this is clear as I need one formula to sort this column


thank you roger

Expert:  Richard replied 3 years ago.
No it is not clear sorry Roger.

What is it you now need me to do?

Change the current formula or a new formula?

Customer: replied 3 years ago.

change current formula in M to include an and or for line 573 which has its Y in K


will upload the amended sheet now

Expert:  Richard replied 3 years ago.
ok, upload the sheet and give me the new File ID please

Customer: replied 3 years ago.

file id Accounts\Billy\Mar2013 432356 amended.xls uploaded

Expert:  Richard replied 3 years ago.
ok, this is a new question though Roger, as I did your initial request which works.

I will do this as a followup, but can I ask please you rate me here before I do this new request?
Richard and 4 other Computer Specialists are ready to help you
Customer: replied 3 years ago.

have rated you excellent

Expert:  Richard replied 3 years ago.
Thanks

I have done it for you, you can download it from www.wikisend.com under File ID 857942

Is this what you required?
Customer: replied 3 years ago.

have downloaded but I get no values in any M cell


this should be the continuation of previous sheet 432356 which was half correct

Expert:  Richard replied 3 years ago.
No you wont, as no K cells have a Y

"Include an and or for line 573 which has its Y in K"
Customer: replied 3 years ago.

my copy has Y in k see line 573 will upload again as maybe you copy is fragmented

Expert:  Richard replied 3 years ago.
I do too, but you also said

=IF(AND(J573="y",D573>=0),D573,"") plus K= Y

So J573 needs a Y which it does not have.

Customer: replied 3 years ago.

formula for column M


 


k has Y in 573 to bottom of sheet with 5 exceptions that are J=Y


so we need what we have plus K=Y


 


IF(AND(J573="y",D573>=0),D573,"") and /or K= Y


it is difficult to explain


roger

Expert:  Richard replied 3 years ago.
It cannot be and or or Roger, it has to be one of the following

So in laymans terms.

J573="y",D573>=0

OR K = "Y"

Is this what you want?
Customer: replied 3 years ago.

I want


if J2=Y then M2=D2 (and or) if K=Y then M2=D2 all in one formula

Expert:  Richard replied 3 years ago.
It cannot be And or Or Roger it has to be one or the other,

Customer: replied 3 years ago.

have this sorted now


 


another sheet that needs sorting here are the facts


have uploaded it as


http://wikisend.com/download/506262/roger march 13 op.csv


 


M £ requires value from D only when order number A=amz026,=amz204, amz202, amz203, BTF and J=Y


 


N = Euro value from D only when order number A


=amz028, amz403,amz302, amz402, amz304, amz404, and J=Y


 


 


O = $ value from D when L = Y and order number =A =amz002, amz102, amz103, amz104, amz105, amz106, amz107,


amz108, amz109, amz110, amz111, amz112, and OP


 


P = yen value from D when L = Y and order number =A =amz249, amz503,


 


Q= £non requires value from c only when order number A=amz026,=amz204, amz202, amz203, BTF and J=Y


 


R = euronon Euro value from C only when order number A


=amz028, amz403,amz302, amz402, amz304, amz404, and J=Y


 


S=$ non


O = $ value from C when order number =A =amz002, amz102, amz103, amz104, amz105, amz106, amz107,


amz108, amz109, amz110, amz111, amz112, and OP and J=Y


 


 


P = yen value from C when order number =A =amz249, amz503, and J=Y


 


thank you roger


 















£Euro$YEN£nonEuronon$nonYennon
Expert:  Richard replied 3 years ago.
ok, but I will need a new question from this Roger.

Can you make it here please.

http://www.justanswer.co.uk/profile.aspx?PF=32989067&FID=12299

Expert:  Richard replied 3 years ago.
This is done Roger.

www.wikisend.com File ID 182682

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

entered the formulas but they show blank see this upload to you


 


http://wikisend.com/download/577116/roger march 13 op.csv

Expert:  Richard replied 3 years ago.
Reply on the new question please Roger, not this one.