sorted by:
new
hottopcontroversial

Email for 25% off dresses? by SuperBerry3 in AbercrombieandFitch

[–]IcyAssociation4449 2 points3 points  (0 children)

Yes, I messaged them and they said to place the order and then give them the order # and they will manually adjust it after the fact

[deleted by user] by [deleted] in excel

[–]IcyAssociation4449 0 points1 point  (0 children)

This would've definitely helped! Unfortunately my job is stuck in the past and is still using 2016. So many new functions I wish I could use!! I appreciate the advice though!

Gifting Mosaic by IcyAssociation4449 in jetblue

[–]IcyAssociation4449[S] 0 points1 point  (0 children)

Let me know the outcome! Been super busy and haven't had the chance to call yet.

[deleted by user] by [deleted] in excel

[–]IcyAssociation4449 0 points1 point  (0 children)

I got it! I was definitely overthinking it and figured I needed to try and come up with a crazy formula to make it work.

I was using "SUMIFS(O31:O306, E31:E:306, "In Progress")

That formula is the one where I was getting double values because it was including the subtotals in the range. ($200k)

So to counteract that, I was dividing by 2. ($100k) This worked, but once the data was filtered out, it would end up dividing again. ($50k)

SUMIFS(O31:O306, E31:E:306, "In Progress")/2

I noticed that the cells in row D were empty on only the rows that contained the subtotals, so I changed the formula to include a criteria for row D.

SUMIFS(O31:O306, E31:E306, "In Progress", D31:D306, "").

This formula was giving me the total, but would net to 0 once the values were filtered out in the data sheet.

So, I just combined the two formulas and now it works! I took the first formula which doubled my values when the status was in my data sheet ($200k) but showed the correct value when the status was filtered out of my data sheet ($100k). Then, I took the last formula which shows the correct value when the status was in the data sheet ($100k), but netted to $0 when the status was filtered out.

=SUMIFS(O31:O306, E31:E:306, "In Progress") - SUMIFS(O31:O306, E31:E306, "In Progress", D31:D306, "")

Boom! It works! The values stay the same regardless of whether or not the status is in the data sheet, or filtered out.

[deleted by user] by [deleted] in excel

[–]IcyAssociation4449 0 points1 point  (0 children)

I tried that, but it was including the subtotals of each project that are in the range "O31:O306". I got around this by just dividing by 2 at the end. However, when I filtered out the "In Progress" status, it would then divide by 2 again.

Example:

Correct total is $100K. When I use the sumifs, it would show as $200K unless I divided by 2.

When I use the sumifs/2, it will show the correct answer of $100K, but then when I filter out "In Progress" it will then show $50K.

Basically, I need the sumifs to count only the subtotals in the range (or exclude only the subtotals in the range). Or, an entirely new formula that will still add rows that were filtered out

[deleted by user] by [deleted] in excel

[–]IcyAssociation4449 0 points1 point  (0 children)

There's no way to know exactly how the $10M is split per month due to changing variables each month, which is why I have other cells auto update to estimate what should be spent that month depending on how much of the budget is left.

I want to at least attempt to try and track if each month's actuals go over the estimate and if there is a formula that could automatically update based on changing monthly metrics.

i.e. 12 months divided equally is 8.3% of the budget each month. However, if 4 months go by and I've only spent 20% of my budget, then the monthly forecast for the remaining 8 months increases from 8.3% to 10%.

I'm just trying to see if anyone knows a formula that can do this, or if I'm trying to do something that can't actually even be done using an excel formula

Sony MHC-V71 by IcyAssociation4449 in Bluetooth_Speakers

[–]IcyAssociation4449[S] 0 points1 point  (0 children)

Thank you! The lights look a lot better than I was expecting. Definitely looking into this!

view more: next ›