Thanks guys. I used MID to separate the flop cell out into individual cells. Now just have to write a bunch of IF/OR statements to analyse the boards.

Excel beginner here, is it possible to have excel send me iMessages from pc to phone if certain conditions are met on single or multiple spreadsheets?

Probably, but I couldn't tell you how to do it. What's changing in these spreadsheets that means you need an imessage, are they pulling in external data themselves?

Yes, they are pulling data from another program I have running which updates fairly often. Have thought about setting it up via email but the process is too slow. I guess I don't need to use imessage, something like WhatsApp or the like would be more than good enough for when I am out of the office.

I'm using Excel with Office 365. I have two questions.

1.)
What should I add to this formula so that if nothing is entered in the cells the formula refers to, I won't have anything show in the cell? Right now, if nothing is entered in the referring cells "#DIV/O!" will show in the cell. I'd like it to remain blank if nothing is entered in those cells.

This is the formula:
=AVERAGE(T48:T52)


2.)
I am also trying to compare two cells and show the percentage increase or decrease between them. I'd also like for the cell to not show anything if nothing is entered into either of the two cells I'm trying to compare.

This is the formula:
=(C67-D67)/D67

This compares the increase or decrease from cell C67 to D67.

What can I add to this so it will not show anything at all in E67 if either C67, or D67 is blank?


Thanks in advance for the help!!!

=IFERROR(AVERAGE(T48:T52), "")

That'll leave a blank.


I'm not sure if I have you right or not, but I think this works:

=IF(AND(C67>0, D67>0), (D67-C67)/C67, "")

It worked great for:
=IFERROR(AVERAGE(T48:T52), "")

When I enter:
=IF(AND(C67>0, D67>0), (D67-C67)/C67, "")

I get #VALUE! in the cell if nothing is entered in D67.

Maybe I did something wrong?

It should work - I tried it with all combos and all of them worked for me. I have updated the formula to make it a bit better (it'll show something if either C67 or D67 have negative values; it'll only be blank if either is blank)

=IF(AND(C67<>"",D67<>""),(D67-C67)/C67,"")

Aha, I didn't see the edit. A further nicer way to do it would be:

=IF(AND(ISNUMBER(C67), ISNUMBER(D67),(D67-C67)/C67,"")

That lets you do it regardless of what's in those cells. If they're both numbers, it'll check the difference.

How do I do a averageif where I want to average the columns of D1: D10 when A doesnt equal A1?

=AVERAGEIFS(D$17: D$24, C$17:C$24, C17, A$17:A$24, <>A17)
this doesnt seem to work and I cant figure out what else I can do.

thanks for any help

Not quite sure what your question is, but your cell reference needs to be in the format - "<>"&A17

If you change that in your formula you should be averaging any number in column D when the corresponding number in column C = C17 and the corresponding number in column A doesn't equal A17

that did the trick. Thanks

I'm having trouble making an excel grade book that calculates weighted grades when some categories are still empty.

For example, let say we have a class with the following categories:
Homework (weighted at 25%)
Tests (weighted at 50%)
Projects (weighted at 20%
Participation (weighted at 5%)

I'm able to get everything working as desired as long as I have something entered for each category. The problem comes in when one (or more) categories are empty. For example the participation grade doesn't get added until the end of the quarter, and projects aren't due until we get closer to the end of the quarter.


Right now, I have each category averaged out. Then I have each average grade multiplied by the weight % for that category, then I have the spreadsheet add those weighted grades added up for the final grade.

If one (or more) of my categories are empty, I get #VALUE! in the cell I want to display my final grade.


Using the example above, let's say I only have entries for homework and tests and nothing for projects or participation.

Any idea how I should calculate a class grade when one (or more) categories are still empty?

Thanks!!!

So here we have your Excel sheet. Top row is the categories, Left Column is the students. 2nd row is the weights assigned to each category. The formula you want in F3 is:

=SUM((B3*B$2),(C3*C$2),(D3*D$2),(E3*E$2))/SUMIFS(B$2:E$2,B3:E3,"<>")

There are two parts to this.

1 - The numerator is just the sum of the score for each category (B3) times the weight of that category (B2). Do this for each category (columns B through E). The $ just means that you are freezing row 2 so you can copy the formulas down to the following rows

2 - The denominator looks at each category cell (B3) for that student and if it isn't blank ("<>") it adds in the weight for that category (B2).

Numbers in red are what you should get from the formula

Thank you Pudley, but I'm not sure I follow.

Right now, this is what I have and where things are:

C33 - My Homework average
C34 - The weight for Homework (25%)

H33 - My Tests average
H34 - The weight for Tests (50%)

K33 - My Projects average
K34 - The weight for Projects (20%)

N33 - My Participation average
N34 - The weight for Participation (5%)

I35 - My Grade - this is the formula I currently have to calculate my grade:
=(E33*E34)+(H33*H34)+(K33*K34)+(N33*N34)


Could I keep the way things are and just use a different formula is I35 to show my grade? Or would I need to do things a little different?


Thanks so much for the help!!!!!

Do you have anything else in rows 33 or 34?

Do you have one worksheet per student or are they all on the same worksheet?

It's just a one person grade spreadsheet. Here's a screen shot fo the 1st quarter. Everything works as expected because each category has at least one entry. if I delete the participation entry, I would get the error - #VALUE! instead of an actual final grade.

Hopefully this helps explain where things are in the spreadsheet I have. Be easy, I'm clearly not great with excel

[IMG][/IMG]


Right now, I'm using this formula to calculate the final grade: =(E33*E34)+(H33*H34)+(K33*K34)+(N33*N34)

Is there a different formula I can use that would calculate the final grade even if there are not entries in every category?

Thanks!!!

Do you want a blank (i.e. didn't participate) to just be treated as a zero?

Try this formula:

=SUM((E33*E34),(H33*H34),(K33*K34),(N33*N34))/SUMIFS(E34:N34,E33:N33,"<>",E34:N34,">0")

The blue part of the formula is basically what you have right now. It's adding all of the partial scores as the numerator.

We add in the green part to eliminate the components that don't have a value yet. Here's how it works:

The first entry (E34:N34) is taking all of the values in row 34 beginning at E34 (your homework weight) and ending at N34 (your participation weight). These cells are what it is going to add together.

The second entry (E33:N33) and third entry ("<>") are going to select only the columns from row 33 that have a value in them. So if you have a blank for the participation score, the formula will ignore that column.

The fourth (E34:N34) and fifth (">0") entries are only going to select the columns in row 34 that have numbers in them. So it will ignore the columns that have text in them to avoid giving errors.

It will add all of the values that meet both criteria (it has a value in row 33 AND it has a number in row 34). That's going to be the denominator

Thank you pudley4, but I must be doing something wrong. I copied the formula you provided into I35 in my spreadsheet and it didn't seem to do correct things. It will still work properly if all categories have entries, but I still get the same #VALUE! error if one, or more categories are empty.

Hmm, not sure why you're getting that error. Can you send me a copy of the worksheet?

jeffanderson4444 [at] gmail [dot] com

I'm using Excel 2013 but it shouldn't be significantly different from your Office 365 version.

I have a table in a spreadsheet that tracks options trades. Each row shows a separate trade, with a status of "OPEN" or "CLOSED" and the associated dates for opening and closing the trade. Regardless of the trade status, it also tracks the trade risk as a dollar amount.

I would like to track the daily amount of outstanding risk on the portfolio. It is easy for me to see the current outstanding risk by sorting for "OPEN" trades, but I would also like to chart this historically.

My thought is to start a new sheet with dates (Jan 1 - Dec 31), then have a formula that identifies and SUMs the outstanding risk on each date, based on whether the date listed falls between (or matches) the open and close dates of each trade.

In other words, if the trade is in an OPEN status on the given date, add its' risk amount to the sum. By doing this, i would be able to calculate and chart outstanding risk looking back in time.

Thanks!

I have two questions. I will break them into different posts such that I have at least plausible deniability.

Yeah, you need SUMIF (or SUMIFS) for this, should be straightforward.

Use this:

=SUMIFS(RANGE OF TRADE VALUES,RANGE OF TRADE OPEN DATES,"<="&A2,RANGE OF TRADE CLOSE DATES,">="&A2)

In this example, the dates go from 1/1/17 in A2 to 12/31/17 in A366.
This formula starts in cell B2 and copies down.

This formula looks at the dates of the trade to make the calculation.

You can use SUMIF and compare against the OPEN or CLOSED status like this:

=SUMIF(RANGE CONTAINING OPEN/CLOSE STATUS,"OPEN",RANGE CONTAINING TRADE VALUES)