Author Topic: Excel wizards (or Google Sheets wizards)  (Read 2800 times)

LadyStache in Baja

  • Pencil Stache
  • ****
  • Posts: 699
    • My Casa Caoba: Making meaning in Mexico
Excel wizards (or Google Sheets wizards)
« on: October 13, 2017, 10:10:28 AM »
I need to make a function that is dependent on date.

So if the value of column A is a date that falls between January and June, put "2" in Column B, otherwise put a 1.

Then I will multiply Column B by the values in Column C.

Maybe it is easier to do it without Column B. So if A is between January and June, multiply C by 2.

Ideas??

I'm technically using Google sheets, but the formulas are usually the same. How can I do this?!


MonkeyJenga

  • Walrus Stache
  • *******
  • Posts: 8894
  • Location: the woods
Re: Excel wizards (or Google Sheets wizards)
« Reply #1 on: October 13, 2017, 10:25:42 AM »
Is there a chance you'll need to change the multipliers or the Jan-Jun range in the future?

And is this a specific year, or Jan-Jun of any year?

solon

  • Handlebar Stache
  • *****
  • Posts: 2359
  • Age: 1823
  • Location: OH
Re: Excel wizards (or Google Sheets wizards)
« Reply #2 on: October 13, 2017, 10:29:41 AM »
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)

BTDretire

  • Magnum Stache
  • ******
  • Posts: 3074
Re: Excel wizards (or Google Sheets wizards)
« Reply #3 on: October 13, 2017, 10:37:00 AM »
Anyone have a good tutorial for OpenOffice?
I'm old so a repetive style would help more.

marielle

  • Pencil Stache
  • ****
  • Posts: 860
  • Age: 30
  • Location: South Carolina
Re: Excel wizards (or Google Sheets wizards)
« Reply #4 on: October 13, 2017, 10:37:37 AM »
What is the actual task you're trying to do and why? This could be an XY problem:

http://xyproblem.info/

If this is just a simple one-time thing then that IF statement would work.

ToeInTheWater

  • Stubble
  • **
  • Posts: 142
  • Location: Central Indiana
Re: Excel wizards (or Google Sheets wizards)
« Reply #5 on: October 13, 2017, 10:44:48 AM »
=+INT(   (MONTH(U68)-1)   /6)   + 1

this would be year independent


Proud Foot

  • Handlebar Stache
  • *****
  • Posts: 1160
Re: Excel wizards (or Google Sheets wizards)
« Reply #6 on: October 13, 2017, 12:53:58 PM »
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)

you could also simplify this further to

=IF(MONTH(A1)<7,2,1)

This way you will not have to update it every year.

Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1
« Last Edit: October 13, 2017, 12:56:46 PM by Proud Foot »

solon

  • Handlebar Stache
  • *****
  • Posts: 2359
  • Age: 1823
  • Location: OH
Re: Excel wizards (or Google Sheets wizards)
« Reply #7 on: October 13, 2017, 01:02:22 PM »
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)

you could also simplify this further to

=IF(MONTH(A1)<7,2,1)

This way you will not have to update it every year.

Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1

Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!

LadyStache in Baja

  • Pencil Stache
  • ****
  • Posts: 699
    • My Casa Caoba: Making meaning in Mexico
Re: Excel wizards (or Google Sheets wizards)
« Reply #8 on: October 22, 2017, 08:02:17 AM »
Thanks so much! I'll look into understanding what you guys wrote.

For those that asked, my goal is this:

Column B is the harvest date of certain vegetables. If the harvest falls between Jan and June of the year, then I want to double the amount I plant (because that's when our CSA is running).

So I'll have Column C which is the beds I'll plant.

So if I normally plant 2 beds of zucchini, during CSA season, I want to plant 4.

LadyStache in Baja

  • Pencil Stache
  • ****
  • Posts: 699
    • My Casa Caoba: Making meaning in Mexico
Re: Excel wizards (or Google Sheets wizards)
« Reply #9 on: October 27, 2017, 11:12:12 AM »
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)

you could also simplify this further to

=IF(MONTH(A1)<7,2,1)

This way you will not have to update it every year.

Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1

Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!

This is awesome!!!! How do I tell it month<6 AND day < 10???

Proud Foot

  • Handlebar Stache
  • *****
  • Posts: 1160
Re: Excel wizards (or Google Sheets wizards)
« Reply #10 on: October 27, 2017, 01:39:47 PM »
This one works in Excel:

=IF(AND(A1>DATE(2017,1,1),A1<DATE(2017,6,30)),1,2)

you could also simplify this further to

=IF(MONTH(A1)<7,2,1)

This way you will not have to update it every year.

Edit: If you wanted to exclude column B then your formula would be =IF(MONTH(A1)<7,2,1)*C1

Yeah, that's much more elegant. Plus, you got the 2 and the 1 in the right order!

This is awesome!!!! How do I tell it month<6 AND day < 10???

You would want to embed an IF statement within the TRUE part of the first IF statement. So: =IF(MONTH(A1)<7,IF(DAY(A1)<11,2,1),1))

MDM

  • Senior Mustachian
  • ********
  • Posts: 11473
Re: Excel wizards (or Google Sheets wizards)
« Reply #11 on: October 27, 2017, 03:57:55 PM »
If the harvest falls between Jan and June of the year, then I want to double the amount I plant (because that's when our CSA is running).
This is awesome!!!! How do I tell it month<6 AND day < 10???
Assuming you mean "if the harvest falls between 1-Jan and 10-June inclusive, then I want to double..." you could use

=IF(OR(MONTH(A1)<6,AND(MONTH(A1)=6,DAY(A1)<=10)),2,1)

LadyStache in Baja

  • Pencil Stache
  • ****
  • Posts: 699
    • My Casa Caoba: Making meaning in Mexico
Re: Excel wizards (or Google Sheets wizards)
« Reply #12 on: October 27, 2017, 04:27:23 PM »
This is great! I love taking out the mindless parts of my job!


It works! :D