Jump to content

Excel Question 825 revB, subsection 2, clause 4.


Recommended Posts

**Eventually***

 

This will be an easy one for the boffins on here hopefully.

 

 

I need to work out what the cost of each item in Column A is between the dates in C&D, the cost is different each year so need a formula to only count the "Networkdays" in each year columns M-P and can then do the calculation from the columns I-L.

 

Will then be able to do the same for Columns E&F to give a comparison.

 

Had a look online and can’t seem to find anything pertinent 

 

 

 

 

34369253_10204629511172997_5680654265450

Link to post
Share on other sites

Cheers Surf.

Works a treat.

Just had to change the $'s to be able to drag down the columns for the rest of it to work.

I wasn't far away with what i was trying but just needed the final tweak.

 

Thanks again.

Link to post
Share on other sites

I'm just baffled that anyone could make head nor tail of that as the info doesnt' line up with the detail in your post for me ie. there's noting in column A and no dates in C&D etc.

The first thing I posted that didn't work had the correct column numbers. Good job surf was able to decifer what I actually meant.

The power of the genie

Link to post
Share on other sites
  • 2 months later...

In my spreadsheet, I have a date column with various dates. Is it possible to have Excel change the colour of a line, e.g. one week before deadline of each date?

 

And when the deadline is reached, can Excel change another column from e.g. “ongoing” to “done”?

 

All help much appreciated.

Link to post
Share on other sites

In my spreadsheet, I have a date column with various dates. Is it possible to have Excel change the colour of a line, e.g. one week before deadline of each date?

 

And when the deadline is reached, can Excel change another column from e.g. “ongoing” to “done”?

 

All help much appreciated.

http://www.quickbooks-training.net/excel-tutorial-conditional-formatting-to-indicate-upcoming-or-overdue-payments/

 

I used a derivative of that, but is so much easier copying the link than trying to explain it.

Link to post
Share on other sites
  • 1 year later...

Thought I'd add to this thread vs starting yet another Excel thread...

I'm way over my head working on some Excel stuff... I'm mostly copying and amending formulas with a lot of trial and error (and then checking tweaking until I know the results are correct.) 

I currently have this formula which is correct:

=(COUNTIFS('RAW DATA'!$AQ:$AQ,"1",'RAW DATA'!$EA:$EA,1))/$C$5

 - it gives me the % where AQ=1 and EA=1 as a proportion of C5

How do I update the above to include AI  being more than  1? 

I.e. I only want to see the proportion of AQ=1 where AI is more than 1, and EA is 1 (as a proportion of C5) 

I tried this

=(COUNTIFS('RAW DATA'!$AQ:$AQ,"1",'RAW DATA'!$AI:$AI,">1",'RAW DATA'!$EA:$EA,1))/$C$5

But it just returns the same value as my first formula. 

 

Any help would be much appreciated :)

 

 

 

 

 

Link to post
Share on other sites
1 hour ago, Tosh said:

I'm so out of excel practice it's no longer funny but wondering why your AQ value is "1" not 1. If it's a number it doesn't need the ""

I've just been copying formulas that someone else put together... that's how they had it, and it worked.. maybe because sometimes the value is a "yes" or "no" rather than a number, so I've just kept it consistent

Link to post
Share on other sites
11 hours ago, Senrab_nhoj said:

Step 1: Have you confirmed if you just run this for AI independently with count if it is returning any values?

 

yeah, the count comes up correct. what I'm basically trying to say is where condition 1 AND condition 2 are met,  what is the count for condition 3, as a proportion of those who meet condition 1 and condition 2. At least I think that's what I'm trying to do... have spent so long on this that nothing really makes sense anymore

 

 

 

Link to post
Share on other sites

Ah. I somehow managed to figure it out myself... needed to use 2 cells instead of one, but should be able to combine them once my brain isn't feeling so dissolved. 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...