Multiple conditions and multiple actions-- Can it be done?

[HeatherBelle79]

Before I spin my wheels too much on something, I thought I'd pose the question here. I kind of wrote out my logic for what I want so that it's clear--I hope! (I am NOT a coder and I just make spreadsheets for fun generally HAHA so bear with me; it's clear to me)

IF Today's date is between a range AND a total is >= 250
Add $25 to the total you get when you perform
[a string of nested IFs that's already in place that works that is essentially the following:
IF the total >= 600, multiply it by .16, if its >= 450 *.14, >= 300 *.12, >= 150 *.1
ELSE 0

Does this make sense? I think I have too much going on and it can't be done. Previously, I just had 2 spreadsheets--one for use between the date range and one for the rest of the year, but I was hoping to have to only keep and maintain one if possible

TIA!

[jeffreybrown]

Hi Heather,

This makes sense and I can think of a couple of ways this could be done, but can you compose a sample workbook with what you expect as the answer for a given scenario?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[HeatherBelle79]

Thank you!

Here is the only pieces of my workbook that are relevant to this. It is basically capturing a scenario in which you spend so much, you get so much free. But there is a period of the year where you get an extra amount.

[jeffreybrown]

How about...

=C12*LOOKUP(C12,{250,300,450,600},{0.1,0.12,0.14,0.16})+IF(AND(TODAY()>=G1,TODAY()<=H1),25,0)

In G1 = 3 Jan 18
In H1 = 31 Mar 18

[HeatherBelle79]

Awesome! That works. I didn't know about that short-cut... array? I think is the right word? Makes quick work of what I had written so long ago LOL! So, I put the date range (H1 and I1) and today (H2) in cells then nested Your Lookup function and it works

=IF(AND(H2>=H1,H2<=I2),(C12*LOOKUP(C12,{250,300,450,600},{0.1,0.12,0.14,0.16})+25),(C12*LOOKUP(C12,{150,300,450,600},{0.1,0.12,0.14,0.16})))

Thanks so much!!

[jeffreybrown]

You are very welcome Heather and thanks for the feedback.

Yes, the Lookup function (without the need for being entered as a CSE) is like an array function which can make quick work of those multiple If statements.

[HeatherBelle79]

I now want to see if I can convert an impossibly long formula in another place on the spreadsheet (more nested IFs) to use the LOOKUP function for at least parts of it (so long that it required me to write the thing in Word so I could see where everything went bc Excel wouldn't even autocorrect to add all the end parens LOL) #learnsomethingneweveryday

[HeatherBelle79]

I did it!

I went from this:
= IF(P5="Next-Day Air ", (IF(Q4<69.5, 41.95, ((Q4*0.1)+(35*(ROUNDUP(Q4/500,0)))))), IF(P5="Second-Day Air ", (IF(Q4<69.5, 26.95, ((Q4*0.1)+(20*(ROUNDUP(Q4/500,0)))))), (IF(P5="3-Day Shipping ", IF(Q4<69.5, 16.95, ((Q4*0.1)+(10*(ROUNDUP(Q4/500,0))))), (IF(Q4<69.5, 6.95, Q4*0.1))))))

to

IF(Q4<=69.5, LOOKUP(P5,{ "3-Day Shipping", "Next-Day Air", "Normal Shipping", "Second-Day Air"},{16.95,41.95,6.95,26.95}), IF (Q4>69.5, ((Q4*.1) + (LOOKUP(P5,{ "3-Day Shipping", "Next-Day Air", "Normal Shipping", "Second-Day Air"},{10,35,0,20})*(ROUNDUP(Q4/500,0)))), FALSE))

They perform the same thing, but it's MUCH tidier "code" (what little html I've done, my prof was like, "You have such tidy code" LOL) and it was a heck of a lot easier to get all the close parens in place (You can probably read it just fine, but it's to calculate shipping based on the method. There's the minimum charge/base charge [whichever is greater] + an expedited surcharge for each fraction of $500) It will also be easier to update if/when they change their shipping rates.

Thanks again! I couldn't get it to work initially, but some Googling showed me I needed to have my lookup vectors in alphabetical order. I also found out Excel doesn't like alphanumeric soup, but I got the workaround on that one

[jeffreybrown]

Great news Heather. Glad you were able to chug your way thru and get a nod of approval from the prof. Good job.