Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

Excel 2007. Detailed/Complicated IF(AND) or IF(OR) formulas needed.

I've included a spreadsheet that has some manually entered data on a few rows.  I've included text boxes on the spreadsheet with details concerning my needs for formulas.

I may be trying to get an "IF" statement to do too much.

Please advise.
C--Users-dan-Desktop-Payout_to_Pool.xlsx
0
baleman2
Asked:
baleman2
  • 6
1 Solution
 
ProfessorJimJamCommented:
please find attached.

i created two tables  that reside inside named ranges  MyTable1 and MyTable2

then i used the vlookup formula with if function.

the formulas in workbook are self explanatory
C--Users-dan-Desktop-Payout_to_Pool.xlsx
1
 
baleman2Author Commented:
I understand the formulas in Columns Q and S - now that I see them.

I'm not familiar with VLOOKUP.  I can see on Sheet 3 the information posted there, but I don't know how you've used the "vlookup formula with if function".  Can't find the formulas - are they hidden?

Can you explain?

Everything works!
0
 
baleman2Author Commented:
Because I'm unfamiliar with VLOOKUP, I'm now unsure if the data shown in Columns E and G are the result of the VLOOKUP function or not.

On my original spreadsheet (although Columns E and G displayed data) I keyed in the data in those columns manually.

To display the multiplier without keying it in, I thought there would be a formula in E2 as follows:
=IF(C2-A2<15,.010,.005)

The dates shown in A2 and C2 will be pulled in by querying a database and automatically populated.  Then, the formula in E2 would automatically populate the cell with either .010 or .005.  I just couldn't get this same IF statement to also display the phrases I needed in G2.

If the "Paper Sent" date were changed in C2 to 05/20/2016, none of the other fields change.  I would expect C2 multiplier to change to .005, but it doesn't.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
baleman2Author Commented:
To make an attempt at further explanation, when this spreadsheet is first opened via querying a database, Columns E and G would be blank.

They would only populate based on the formula placed in Column E as described above.
0
 
baleman2Author Commented:
In an attempt at clarification, I've attached another spreadsheet with more notes.

Don't know if this makes it harder or easier.
C--Users-dan-Desktop-Payout_to_Pool.xlsx
0
 
baleman2Author Commented:
Wrong attachment.  New one is:  Payout_to_Pool_3
C--Users-dan-Desktop-Payout_to_Pool.xlsx
0
 
baleman2Author Commented:
I can't seem to add the new spreadsheet as an attachment.  I'll open a different ticket.
0
 
Glenn RayExcel VBA DeveloperCommented:
Please close this question since it appears to have been answered here: 28965511.

I would have recommended the VLOOKUP options as well as they are succinct and more-flexible than long, nested IF statements.

Regards,
-Glenn
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now