Excel formula help
Current time: 09-19-2018, 06:13 AM
Users browsing this thread: 1 Guest(s)
Author: hmwere
Last Post: elbarto_87
Replies 6
Views 2952

Excel formula help
#1
Dear fellow CivilEAns,
I would like to count the number of "accepted" or "deferred" automatically using excel formulae.
You may write out , in excel, "accepted" and "deferred" a number of times so you may show me using excel formulae how they may automatically be counted say 5 accepted and 10 deferred. the 5 and 10 should be automatically generated using excel and not manually (because this gets hectic).
An image is appended.

[Image: 59313026758841561309.jpg]

Thanks and regards
A STRUCTURAL ENGINEER has the ART OF USING MATERIALS That Have Properties Which Can Only Be Estimated TO BUILD REAL STRUCTURES That Can Only Be Approximately Analyzed TO WITHSTAND FORCES That Are Not Accurately Known SO THAT HIS/HER RESPONSIBILITY WITH RESPECT TO PUBLIC SAFETY IS SATISFIED.
Reply
#2
use formula like below:
=countif(a1:a1000,"Accepted") ===> in cell you'd like to show number of "Accepted"
=countif(a1:a1000,"Deferred") ===> in cell you'd like to show number of "Deferred"
[-] The following 7 users say Thank You to kowheng for this post:
  • shadabg, Diquan, lisine, hmwere, jaks, LiviuM, Dell_Brett
Reply
#3
I want to know,

[Image: 23489574348107923796.jpg]

if i'm having few Levels written in a single column (as per screenshot) and if i want to reverse it, how can i do in excel.
Sometimes such small things troubles a lot, especially when they in huge nos.

Thanks,
Regards,
Shadab

"Dream till you Live, Chase till you Die"
Reply
#4
First select the cells you want to work on.
Then go to DATA-SORT and in the window choose DESCENDING.
In the same window you can choose which column you want to sort first.
"Engineers can do just about ANYTHING" George Phelps
[-] The following 1 user says Thank You to ingenierobou for this post:
  • shadabg
Reply
#5
Dear all,
In continuation with my earlier thread, how can i then know the number of "accepted" or "deferred" that either janet, Dan, Peter, ezra etc each have? Automatically by excel using formulae.

[Image: 23501535877311075986.jpg]

Thanks and regards
A STRUCTURAL ENGINEER has the ART OF USING MATERIALS That Have Properties Which Can Only Be Estimated TO BUILD REAL STRUCTURES That Can Only Be Approximately Analyzed TO WITHSTAND FORCES That Are Not Accurately Known SO THAT HIS/HER RESPONSIBILITY WITH RESPECT TO PUBLIC SAFETY IS SATISFIED.
Reply
#6
Dear hmwere

I haven't done this in Excel before. You have to write a macro to do such operations. However to do this in excel you may have to make some arrangements like in C column from row 1 to 22 you have enter 1. Then from cell A25 onwards you have to list all different names twice. Since there are 9 different names this will fill up for eighteen rows. For each name you have to enter Accepted and Deferred in column B. Then in cell C25 you can enter this formula :
SUMIFS($C$1:$C$22,$A$1:$A$22,A25,$B$1:$B$22,B25). Then click and drag this for entire names. That's (Sad) all....!
Hope this helps... or someone may guide whether this can be done using Pivot tables.
[-] The following 1 user says Thank You to dinu69in for this post:
  • hmwere
Reply
#7
Attached is how I usually tackle such a problem. I used nested if functions returning a binary array with I then take the sun of, just remember to use ctrl+shift+enter when entering formulay (google array formula). Sounds more comlicated then it actually is but I find this to be a rather simple solution that is very flexible.

Regards Elabrto

[Image: 68094347848368489001.jpg]
.
[-] The following 6 users say Thank You to elbarto_87 for this post:
  • andersen3, LiviuM, 3fan, cace-01, hmwere, oanm2000
Reply




Users browsing this thread: 1 Guest(s)