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 The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1280
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Need help making a formula that will assign a defined value

Customer Question

Need help making a formula that will assign a defined value to cell depending on date in given cell. In addition, if date in cell does not match defined date I need to see "End" other side I need to see (K3, O)+P3.

Q2=month to date, H3 represents date of sale.

=IF($Q$2>H3,"End",IF(MONTH(G3)=Q$2, K3,0)+P3)

my formula is working for the first 12 columns in 2013 but when the date begins in 2014 it shows up in both the month for 2013 and 2014; doubling my amounts and providing inaccurate data.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Yegarboy replied 1 year ago.
Welcome To Just Answer!

Could you give me some data for the formula above? This way I can see what you see :)

Also, what is showing up and what it should be? Thanks
Customer: replied 1 year ago.


do you need me to upload the spread sheet?

Expert:  Yegarboy replied 1 year ago.
That would be great :)

You can upload it to www.wikisend.com

After upload copy the File ID number or Download link and paste it here.

Cheers
Customer: replied 1 year ago.


It is on its way, I highlighted the rows in green all the way accross. The two tabs that I am working on are recuring receivables and R1-A Joni Scott

Expert:  Yegarboy replied 1 year ago.
Ok I await your file.

Cheers
Customer: replied 1 year ago.

I have been working on this for several nights and work full time during the day.. Please forgive me I did not mean to be rude... Thank you very much for trying to help me with this project. I have been having a tough time with it.

Expert:  Yegarboy replied 1 year ago.
No worries,

I am here to help anyway I can. I know how frustrating it can be..trust me :)
Customer: replied 1 year ago.

Any update?

Expert:  Yegarboy replied 1 year ago.
You were supposed to be uploading me your file to the link I gave above.
Customer: replied 1 year ago.

I dud the upload twice?? I am not home from work yet but will be in about an 40 minutes. I will send as soon as I am in house.

Expert:  Yegarboy replied 1 year ago.
After you upload your file, Copy the File ID Number or Download link. And Paste it here.

Cheers :)
Customer: replied 1 year ago.

It is on its way, it looks like it went through and nothing was different than last time I uploaded a file. Please let me know asap if you got the file as I have been up since Sunday. I was waiting up this morning thinking you had the file.


 


 


I highlighted the rows in green all the way accross. The two tabs that I am working on are recuring receivables and R1-A Joni Scott.

Customer: replied 1 year ago.

It is on its way, it looks like it went through and nothing was different than last time I uploaded a file. Please let me know asap if you got the file as I have been up since Sunday. I was waiting up this morning thinking you had the file.


 


 


I highlighted the rows in green all the way across The two tabs that I am working on are recurring receivables and R1-A Joni Scott.

Customer: replied 1 year ago.

did it come through? In the past I have worked with another gentleman but I had to pay for it as I was under time constraints. He actually helped start this document. I am about to crash as I older then you and can't stay up 48 hrs straight ;-) enjoy your youth!! I will be back on tomorrow and if i have to pay to ensure that we can get this wrapped up that won't be an issue. Please let me know.

 

Elizabeth

Expert:  Yegarboy replied 1 year ago.
Its not coming through.

Are you uploading your file to www.wikisend.com? <<< Follow this link.

Then Upload your File. And give me the Download link or File ID Number like in this photo.

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here

Attachments are only available to registered users.

Register Here
View Full Image

Attachments are only available to registered users.

Register Here


I'm not sure what you are doing to upload it but I am not getting it.
Customer: replied 1 year ago.

here is everything from the download page. I thought that was for me after we are done working on it.


 


572488


 


http://wikisend.com/download/572488/ResMan Sales and Commissions Ver 4.xlsx


 


ResMan Sales and Commissions Ver 4.xlsx

Expert:  Yegarboy replied 1 year ago.
Ok I got it sorted out :)

For some reason your Formula can't determine the Month Correctly on its own. So add quotes to the Month Result.

Example:
IF($Q$3>H16,"End",IF(MONTH(G16)=6,K16,0)+P16)




Should be...
IF($Q$3>H46,"End",IF(MONTH(G46)="6",K46,0)+P46)

Notice the Quotes around the 6.


I hope this helps :)

Cheers
Customer: replied 1 year ago.

sorry I am in meeting will that formula allow differentiation between march 2013 and march 2014?

Expert:  Yegarboy replied 1 year ago.
Yes because you have already included a > than Date in your formula. :)
Customer: replied 1 year ago.

when I put the formula in the cells that should have end now say value?

Expert:  Yegarboy replied 1 year ago.
Don't actually use the Formula's I gave you. All you need to do is add the Quotes around the Month number in each Formula in your File.

The Formulas were an Example.

Customer: replied 1 year ago.

That is what I did as your formula was relevant to that row only. It did not fix the issue. I went recurring receivables>row 3 and put in "2" around the number representing the month which made the amount clear where it was fine before ?


 

Expert:  Rachel-Mod replied 1 year ago.

Hello,

I’m a moderator for this topic. It seems the Professional has left this conversation. This happens occasionally, and it's usually because the professional thinks that someone else might be a better match for your question. I've been working hard to find a new professional to assist you with your question, but sometimes finding the right professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!

Rachel

Customer: replied 1 year ago.

Thank you, I was tied up in meetings all day yesterday and into the evening. I am a little bit older and can't stay up all night 2 nights in a row. I am still needing help and have not resolved my problem.


 


D3 contants the date the contract was signed.


N2 contains the month the commission is to be paid. The formula below works if the contract reflects a date where the day is on the 1st; if the contract reflects a sign date of 3/15/13 for example the formula does not assign the value needed.


 


=IF($D3<N$2,0, IF(AND($D3>=N$2,$D3<=N2),$E3,0))

Expert:  Rachel-Mod replied 1 year ago.
Thank you for your patience. We will continue the search for a Professional for you.
Rachel
Expert:  The-PC-Guy replied 1 year ago.
Thanks for using JustAnswer. My name is Andrew, I will do whatever I can to answer your question

Attachments are only available to registered users.

Register Here


I am looking at your sheet now, I will let you know if I have any questions.
Customer: replied 1 year ago.

Thank you I am past deadline and have to complete this today no matter what and do not want to manually enter amounts in the columns in white on all those spreadsheets.


 


Thank you for picking up my issue

Expert:  The-PC-Guy replied 1 year ago.
I am happy to help, and believe me I will help you get it completed today. There is no reason why you should have to manually enter anything

based on this formula you wrote above.

=IF($D3<N$2,0, IF(AND($D3>=N$2,$D3<=N2),$E3,0))

that could never work because D3 cannot be >= and <= N2 at the same time

So essentially is this the value you want entered in N3

If the date in D3 is < the 1st of the month in N2, then it should be 0, otherwise it should be the value of E3?

Or does the date in D3 alter the value you want in N3, that is does it effect the value if the date is not on the 1st of the month, as you seamed to indicate above.

Is this correct?

Also is this all you need to have done?
Customer: replied 1 year ago.

Can I upload the file for you to view - in case I am not stating it correctly

Expert:  The-PC-Guy replied 1 year ago.
i already have the file, thanks, Perhaps you can explain the goal.
Customer: replied 1 year ago.

Columns N:AK need to reflect in each row below the number of units as determined by the contract date. So column N will show any units signed into a contract for the month(column header)

Expert:  The-PC-Guy replied 1 year ago.
Sorry Not following, the units are coming from Column E?

Am I supposed to multiply that by the value in Column I? To get a total $ value?

And do partial months need to be calculated, That is if the month in Column N is 3/1/13
and the date in column D is 1/1/13 that would be 2 months correct? But if the date in column D were 1/15/13 that would be 1.5 months?

is this correct so far?
Customer: replied 1 year ago.

No I don't need to account for the $.00 I need to pull over the number of units for that contract into columns N:AK. We calculate the commissions elsewhere. The number of units closed for each month determines the amount of the commission.


 


So I need to see the number of units in column E reflected in the appropriate month the contract was signed.

Expert:  The-PC-Guy replied 1 year ago.
ok, so all of the $ signs in N:AK are just formatting, you are just interested in the number of units, and only if the month heading matches the contract date? and all of the others months should be blank or 0?
Customer: replied 1 year ago.

k, so all of the $ signs in N:AK are just formatting, you are just interested in the number of units,> YES


 


and only if the month heading matches the contract date? NO- I need it to match Month, YEAR. When I date a contract for the 1st day of any of those columns the units carry over just fine, but if the contract starts on any other day of that month the units don't cary over.


 


and all of the others months should be blank or 0? YES

Expert:  The-PC-Guy replied 1 year ago.
ok, i got you now. Month and Year have to match
Customer: replied 1 year ago.

yes regardless of day

Expert:  The-PC-Guy replied 1 year ago.
here is the modified file.

http://ge.tt/9uypEod/v/0?c

click the link, then click to download

let me know if it works for you as it is now.
Customer: replied 1 year ago.

I am on the R1-A tab for Joni and I don't see any formula changes?

Expert:  The-PC-Guy replied 1 year ago.
the formula was put on the recurring receivables tab, did it need to go on multiple tabs?
Customer: replied 1 year ago.

oh no... we are trying to work on the yellow tabs only... I finished out the recurring on my own the night before. The tab tracks something different and explains why you were asking about amounts.. and i kept referencing units.


 


i thought it was in the previous emails. I am sorry- look at the joni tab and the need for month/day/year in columns N:AK

Expert:  The-PC-Guy replied 1 year ago.
ok i will make the changes
Expert:  The-PC-Guy replied 1 year ago.
sorry about the mixup

here is the corrected version

http://ge.tt/29h7fod/v/0?c
Expert:  The-PC-Guy replied 1 year ago.
did you get the last message, with the fixed sheet that has the tab you mentioned with the fixed formulas?

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions